Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

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

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
Andreas Hermle
Asked:
Andreas Hermle
  • 3
  • 3
1 Solution
 
StephenJRCommented:
Try this:
Columns(5).SpecialCells(xlCellTypeConstants).Interior.Color = RGB(219, 229, 241)

Open in new window

0
 
aszabo1Commented:
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
 
aszabo1Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
StephenJRCommented:
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
 
aszabo1Commented:
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
 
Andreas HermleTeam leaderAuthor Commented:
Hi Stephen,

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

Regards, Andreas
0
 
StephenJRCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now