Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Apply a RGB cell fill of 219, 229, 241 to any cells with an entry (number / text)

Posted on 2011-09-16
7
Medium Priority
?
373 Views
Last Modified: 2012-06-21
Dear Experts:

I would like to achieve the following using VBA:

Look for any cells with an entry (number and/or text) in Column E and apply a RGB Fill of 219, 229, 241 to these cells.

Help is much appreciated.  Thank you very much in advance.

Regards, Andreas
0
Comment
Question by:AndreasHermle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 36552277
Try this:
Columns(5).SpecialCells(xlCellTypeConstants).Interior.Color = RGB(219, 229, 241)

Open in new window

0
 
LVL 1

Expert Comment

by:aszabo1
ID: 36552288
Hi Andreas,
I'm actually using Excel 2003 at the moment, but the easiest way I know of to set a cell to a particular RGB value is to make it part of your colour palette. The code to do that is as follows:
 ActiveWorkbook.Colors(1) = RGB(219, 229, 241) 

Open in new window

This particular code sets it as color 1 in your palette, but you can choose any number up to 56.
I know you indicated you wanted to use VBA to locate the non-blank cells and colour them, and I'm sure there are a number of ways to do that. (We can explore those next if you still want to). In my opinion, however, it would be easier to use conditional formatting.
Highlight the first cell in column E where you might want the formatting to apply. Go to the conditional formatting menu and set your first condition to be Cell Value is not equal to "", and set your format on the "Patterns" tab to the colour which you added to your palette. You can then use the format painter to copy the conditional formatting to all other cells in column E.
Hope that works for you. If not, let us know and either I'll be back with another suggestion or another Expert will come to your rescue!
Cheers,
ASz
0
 
LVL 1

Expert Comment

by:aszabo1
ID: 36552301
Oops... should've tested my solution first. I can't make not equal to "" work. However, if you use a cell reference of a cell that you know will never contain a value (e.g. =$IV$65536) then it will work.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 24

Expert Comment

by:StephenJR
ID: 36552313
aszabo1: I thought of CF too, the only reason I didn't was because I didn't know the colour. You can use a formula like this: =E1<>""
0
 
LVL 1

Expert Comment

by:aszabo1
ID: 36552328
StephenJR: Ah, thanks! I think it's been a long day at work. As for not knowing the colour... that's why I suggested adding it to the color palette first. If this color has special significance, it's probably useful to have it always available in the palette.
0
 

Author Closing Comment

by:AndreasHermle
ID: 36552336
Hi Stephen,

that's it. Thank you very much for your professional help. I really appreciate it.

Regards, Andreas
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36552348
aszabo1: thank you, I wouldn't have thought of that method.

Andreas: my pleasure. aszabo1's method is worth an acknowledgement too in my humble opinion.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question