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

 

[canesbr]

7:50 and 7:51 - guess I was doing this simultaneously. Didn't mean to be a buttinski.
But points out BTW that Select Case is Case Sensitive (heh).
Also use of With.
Regards
Brian

 

[Yogi Anand]

Hi Brian:

I am just curious ... what is buttinski? forgive my ignorance!

 

[Ice223]

Guys, Another problem has cropped up on me.

So A29 is x, xx, xxx, etc.
But it is based on a formula, and if there is no imput for the formula, the result that comes up in A29 is #N/A

Then for the macro, it doesn't work when A29 = #N/A

I figured that:
Case Else: Range("G8").Interior.ColorIndex = 0

...would apply to that, but I guess not.

Can something be added to the script to incorporate this?

-Ice

 

[parry]

Hi, yes I forgot about that as well. If a cell is in error then you cant test against its value. Since you liked Yogi's code I'll amend that 4 u...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If IsError([A29]) 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

btw Yogi, Buttinski = Person who 'butts in' (joins without being asked) to a conversation. Im one of those too.

EDIT: btw, following on from Buttinski's comment, if you want to enter X or x and have both formatted then...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If IsError([A29]) Then Exit Sub
    Select Case UCase([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

 

[Yogi Anand]

Hi parry:

Thanks for educating me about 'Buttinski'

However, both you and Brian have every right to come join the party -- it would have been a dull party without you and Brian's joining in the part hosted by Ice!

 

[Ice223]

Hey guys, I hate to keep having to ask for help, but this isn't working quite right.

I want to repeat the script a few times so that the A29 and G8 script works and then I want to have A30 and AT20 to do the exact same thing, however the two events must be independent of each other.

For the script, I tried:

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

'If Intersect(Target, [A30]) Is Nothing Then Exit Sub
If IsError([A30]) Then Exit Sub
Select Case [A30].Value
Case "x": Range("AT20").Interior.ColorIndex = 19
Case "xx": Range("AT20").Interior.Color = vbYellow
Case "xxx": Range("AT20").Interior.ColorIndex = 15
Case "xxxx": Range("AT20").Interior.ColorIndex = 8
Case "xxxxx": Range("AT20").Interior.ColorIndex = 4
Case Else: Range("AT20").Interior.ColorIndex = 0
End Select
etc...

This does not seem to work as each event is not independent of each other.
How can this be fixed?

Thanks,
-Ice

 

[Ice223]

I think this might solve the problem as well...Maybe.

Instead of:

If IsError([A30]) Then Exit Sub

Could I make it so if it is an error, than it just changes the fill cell to white?

If IsError([A30]) Then Range("W8").Interior.ColorIndex = 0

That doesn't work..but something to that effect?


-Ice

 

[parry]

Right idea but instead of exiting on the error you make this an if statement...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If IsError([A29]) Then
    Range("G8").Interior.ColorIndex = 0
Else
    Select Case [A29].Value
    Case "x": Range("G8").Interior.ColorIndex = 19
    Case "xx": Range("G8").Interior.Color = vbYellow
    Case "xxx": Range("G8").Interior.ColorIndex = 15
    Case "xxxx": Range("G8").Interior.ColorIndex = 8
    Case "xxxxx": Range("G8").Interior.ColorIndex = 4
    Case Else: Range("G8").Interior.ColorIndex = 0
    End Select
End If

If IsError([A30]) Then
    Range("AT20").Interior.ColorIndex = 0
Else
    Select Case [A30].Value
    Case "x": Range("AT20").Interior.ColorIndex = 19
    Case "xx": Range("AT20").Interior.Color = vbYellow
    Case "xxx": Range("AT20").Interior.ColorIndex = 15
    Case "xxxx": Range("AT20").Interior.ColorIndex = 8
    Case "xxxxx": Range("AT20").Interior.ColorIndex = 4
    Case Else: Range("AT20").Interior.ColorIndex = 0
    End Select
End If
End Sub

Note that if you have a lot of these and they all have the same formatting options then this could be passed to another procedure instead of repeating the code all the time. I could show you that if you want. In my opinion this is better suited to the Calculate event as this will only run when you do a calculation while this code will run whenever you select a cell on the sheet. In other words its formatting cells quite often and the majority of the time it will be overwriting the same format. Not really a biggie since this code should be preety quick but if you find your book a bit sluggish then switch events.

hth

 

[Ice223]

Thanks Parry, now I'm all set.

-Ice

 

[hedgie]

How would you modify this code so that it can be used in multiple rows? What I mean is this: In Column A each row has a drop down list for an event. (Open, In Progress, More info needed, complete) based on the selection, I want the entire row to change color.