Conditional Formatting Question

[Jamesrwilliams]

Good afternoon all.

I am trying to cond. format a column where another column holds the value.

For example:

Column AT needs to be coloured if column AW is either Green, Yellow, Pink or Blue (these are values (text string) not actual colours). The colour in AT needs to match the colour AW.

 

[HalfAce]

Hello,
Excel doesn't really allow for more than 3 conditions in conditional formatting.
There are a few ways to get around it in some cases, (and future versions of excel will be able to handle more I believe), but for now I usually use a sheet change event for more than 3 conditions. Perhaps along the lines of:

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Columns(49)) Is Nothing Then Exit Sub
With Target.Offset(, -3)
    Select Case Target.Value
        Case "Green"
            .Interior.ColorIndex = 50
        Case "Yellow"
            .Interior.ColorIndex = 6
        Case "Pink"
            .Interior.ColorIndex = 7
        Case "Blue"
            .Interior.ColorIndex = 5
        Case Else
            .Interior.ColorIndex = xlNone
    End Select
End With
End Sub

If you don't want to use VBA then maybe this link will be of some use.
http://www.mcgimpsey.com/excel/conditional6.html

Hope it helps.

[EDIT:]
I forgot to mention, in the Case Else statement you can replace "xlNone" with the color index of whatever color your sheet background is set to if it's not white.

 

[Jamesrwilliams]

Many thanks for your response.

I have entered the above code but nothing happens, do I need to specify the range?

 

[Norie]

James

Where did you enter HalfAce's code?

It should be in the worksheet module of the worksheet where you want the conditional formatting to be applied.

To access that right click the worksheet tab and select View Code.

 

[HalfAce]

No, the two columns it's supposed to work on are already specified.
Norie's question is what applies. (My fault for not mentioning it.)

Right click on the sheet tab for the sheet of interest.
Choose View code.
Paste the code into the large white area that is the sheet code module.
Press Alt+Q to close the VBE and get back to your sheet.
Now try entering one of your colors somewhere in column AW.

That help?

 

[Jamesrwilliams]

Many thanks

Got it working now, I entered as you instructed before but nothing happened. But once I edited a cell and pressed enter it worked perfectly.

Many thanks for your advise.