VBA cell color

[npacker]

Can I call and change cell colors in VBA? I would like some code in the "ThisWorkbook" sub to say something like

If Range("C7").color = "red" Then
Range("D7").color = "red"

or something to that effect. Any help?
Thanks,
Nate

 

[Cbrine]

npacker,

1. Press Alt-F11
2. Select view from the menu, and make sure the vba project manager is visible.
3. Right click on the worksheet name in the project explorer.
4. Select Insert...Module
5. Double click on the module
6. Add the code to the module.
7. From Excel select Tools...Macro....Macros and selct the getcolor Macro.

HTH
Cal

Heya Smitty.

 

[Smitty]

Put the code in a general module and call it from a blank worksheet.

It will list the Color # and color in A1:B56. You can then reference the list.

See http://www.cpearson.com/excel/colors.htm for more detailed info on working with colors, especially those generated with Conditional Formatting.

Smitty

What's up Matt

 

[npacker]

Oh I see, you were just giving me the color list. Thanks! Now my only problem is this. Once I figure out the colors I want, and I put in this code:

Private Sub ColorChange()
If Range("C7").Interior.Color = vbRed Or Range("C21").Interior.Color = vbRed Or Range("C35").Interior.Color = vbRed Then
Range("A6").Interior.Color = vbRed
End If

I've got the code in the "ThisWorkbook" property, and not in it's own module. It doesn't do anything currently, cause it's not being called by anything. How do I get it to be called continuously so when any of those cells change color, it automatically updates?
Nate

 

[npacker]

I'm just bumping this up to the top, cause I really am curious! Oh, and I noticed on my previous code I forgot to put "End Sub" Don't worry, that's not my prolem, it's in the code.
Thanks,
Nate