More than 3 conditional formats

[Ice223]

Is there a way to get more than 3 conditional formats without using VB?

I've done a search, but could only find a few macro scripts and I am kind of clueless about VB.

-Ice

 

[Yogi Anand]

Hi Ice:

Using Conditional Formating the limit is 3 conditions (4 if you consider the basic worksheet formating as an additional 1). To go beyond that, one can use VBA. If you need to discuss this further provide some details on what you are trying to accomplish -- and then let us take it from there!

 

[Ice223]

Alright, here is what I need:

A29 is going to = x, xx, xxx, xxxx, xxxx, or xxxxx.

In G8, I need the fill color to be different for each of the possible values of A29.

This shouldn't be too hard.
Could it also be quickly/briefly explained to me where to enter the script in and how to actually use it. For instance, do I have to hit the play button or will the macro be running in the background the whole time?

Thank-You

-Ice

 

[Yogi Anand]

Hi Ice:

Please install the following as Worksheet_Change event code by right clicking on the sheet of interest ...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, [A29]) Is Nothing Then Exit Sub
    Select Case [A29].Value
        Case "x": Range("G8").Interior.Color = vbBlue
        Case "xx": Range("G8").Interior.Color = vbRed
        Case "xxx": Range("G8").Interior.Color = vbGreen
        Case "xxxx": Range("G8").Interior.Color = vbYellow
        Case Else: Range("G8").Interior.ColorIndex = 0
    End Select
End Sub

The code will fir automatically if the entry in cell changes -- and the background color of cell G8 will change according to the entry in cell A29.

Let us know how irt works out for you!

 

[Ice223]

It works great.

...Except that in A29, the # of x's are a result of a formula and so the macro doesn't recognize the x's even though they appear the same.

It only works when I manually enter x's into A29.

Any way around this?

 

[Yogi Anand]

Hi Ice:

You may want to change the code to ...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    'If Intersect(Target, [A29]) Is Nothing Then Exit Sub
    Select Case [A29].Value
        Case "x": Range("G8").Interior.Color = vbBlue
        Case "xx": Range("G8").Interior.Color = vbRed
        Case "xxx": Range("G8").Interior.Color = vbGreen
        Case "xxxx": Range("G8").Interior.Color = vbYellow
        Case Else: Range("G8").Interior.ColorIndex = 0
    End Select
End Sub

With this changed code, when the formula changes the result in cell A29 and any cell is selected, color of cell G8 changes as specified.

Would this work for you?

 

[Ice223]

YES!
Excellent! It's perfect.

Thanks so much.

-Ice

 

[parry]

Hi, Im sure Yogi wont mind me answering on his behalf.

Yes the Change event is only triggered by a manual change. There is a calculate event though that runs upon a calculation so this is what you can use instead of the Worksheet_Change...

Private Sub Worksheet_Calculate()
    Select Case [A29].Value
        Case "x": Range("G8").Interior.Color = vbBlue
        Case "xx": Range("G8").Interior.Color = vbRed
        Case "xxx": Range("G8").Interior.Color = vbGreen
        Case "xxxx": Range("G8").Interior.Color = vbYellow
        Case Else: Range("G8").Interior.ColorIndex = 0
    End Select
End Sub

Perhaps a more efficient way to incorprate a change either manually or by a calculation & to only run the code if needed is as follows....

In stand alone module (eg Module1) before other procedures.

Public MyA29Value As Variant

In ThisWorkbook module

Private Sub Workbook_Open()
'Change reference from Sheet1 to the name of your sheet
MyA29Value = Sheets("Sheet1").Range("A29").Value
End Sub

In Your sheet module

Private Sub Worksheet_Calculate()
If MyA29Value <> [A29] Then
Call Worksheet_Change([A29])
MyA29Value = [A29]
End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, [A29]) Is Nothing Then Exit Sub
    Select Case [A29].Value
        Case "x": Range("G8").Interior.Color = vbBlue
        Case "xx": Range("G8").Interior.Color = vbRed
        Case "xxx": Range("G8").Interior.Color = vbGreen
        Case "xxxx": Range("G8").Interior.Color = vbYellow
        Case Else: Range("G8").Interior.ColorIndex = 0
    End Select
End Sub

 

[canesbr]

Maybe this:

Private Sub Worksheet_Calculate()
    With Range("G8").Interior
        Select Case LCase([A29].Value)
            Case "x": .Color = vbBlue
            Case "xx": .Color = vbRed
            Case "xxx": .Color = vbGreen
            Case "xxxx": .Color = vbYellow
            Case Else: .ColorIndex = 0
        End Select
    End With
End Sub

Regards
Brian

 

[Ice223]

Yogi's worked perfectly for me
Thanks Parry and Brian for taking the time to help.
I should be good for now.

Thanks,
-Ice