Conditional Formatting for more than 3 conditions

[Guest]

Hi,
I tried downloading from the xldynamic site, but I think I am doing
something wrong. I get a zip file, and when I extract files I do not get a
..ini file only a .xls file and nothing happens after that.

Can someone help me write a Macro for this? It's simple:

I have to look down a range of columns and rows (Say A1: H30) and, based on
the text there, which could be one of the following letters:
1) G
2) R
3) N
4) Y
5) C
6) W

I need a different color for each type of cell: 1) G=Green with dark green
font for the letter, 2) R=Red with red background and black for letter R, 3)
N= Purple background and Yellow font, 4) Y=Yellow background, black font, 5)
C=Blue background dark blue font 6) W= White background, blue font,

I have no clue how to write a Macro, your help will be highly appreciated,
thanks

 

[Bob Phillips]

It shouldn;t be an xls file but an xla file.

Here is a VBA example that sets the cell colour


Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H30")) Is Nothing Then
With Target
Select Case UCase(.Value)
Case "B": .Interior.ColorIndex = 5
Case "O": .Interior.ColorIndex = 46
Case "P": .Interior.ColorIndex = 7
Case "R": .Interior.ColorIndex = 3
'etc.
End Select


End With
End If


ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Chip Pearson]

Note that Bob's code will run only when the cell's value is
changed by the user, not, as is the case with Conditional
Formatting, when the change is the result of a calculation.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Bob,

Your add-in is absolutely awesome once I got it to work. Kudos!

 

[bkcoughlin]

I'm trying to do exactly that--make it change cell color based on the
calculated value, and have more than 3 conditions. Is there a
modification to this that someone can suggest?

Thanks in advance for any insight anyone can offer.

Brian