Conditional Formatting

**Jay3** · 11-07-2008, 09:32 AM

Hi All,

I'm designing a holiday chart in work, and am using conditional formatting to colour cells.

The problem is I have more than 3 conditions. Consequently I need to use a macro.

I have the following code, but have a couple of questions.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer



    If Not Intersect(Target, Range("G8:GE30", "G35:GH57")) Is Nothing Then
    

        Select Case Target

            Case 0 To 1

                icolor = 6

            
            Case Else

                'Whatever

        End Select

        

        Target.Interior.ColorIndex = icolor

    End If



End Sub

My range select isn't working as I'd like? I need to select 2 individual ranges at the same time?

G8:GE30 and G35:GH57 but based on what is entered above excel seems to be selecting G8:GH57? Can anyone tell me how to fix this?

Also

Can anyone advise how to include a second condition e.g. if the letter L is inserted in a cell, i'd like to shade it grey?

I'd appreciate any help!
Many thanks,
John

**DonkeyOte** · 11-07-2008, 09:39 AM

Hey, you can Union the 2 ranges together like so:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Union(Range("G8:GE30"), Range("G35:GH57"))) Is Nothing Then
    Select Case Target.Value
        Case 0 To 1
            icolor = 6
        Case Else
            'Whatever
    End Select
    Target.Interior.ColorIndex = icolor
End If
End Sub

**Jay3** · 11-10-2008, 06:16 AM

Thanks for that, but can anyone help with conditional formatting for letters also?

ie If I enter a letter I'd also like to shade the cell a particular colour?

**DonkeyOte** · 11-10-2008, 06:31 AM

Simply amend the Select Case as required.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Union(Range("G8:GE30"), Range("G35:GH57"))) Is Nothing Then
    Select Case Target.Value
        Case 0 To 1
            icolor = 6
        Case "A"
            icolor = 3
        Case Else
            'Whatever
    End Select
    Target.Interior.ColorIndex = icolor
End If
End Sub

**royUK** · 11-10-2008, 06:32 AM

Originally Posted by Jay3

Thanks for that, but can anyone help with conditional formatting for letters also?

ie If I enter a letter I'd also like to shade the cell a particular colour?

It would have been better to give a better description, ie use of letters in the first post. Change the case select to

 Select Case Target.Value
Case "H": icolor = 6
Case "S":icolor=3
'etc
Case Else:Exit Sub
End Select
 Target.Interior.ColorIndex = icolor
End If

**Jay3** · 11-10-2008, 04:48 PM

Hi,

I've got it to work was the suggested range and also the conditional formatting with the lettering now works.

Strangely I have to put the case for the lettering before the case for the numbering, otherwise it doesn't function.

Anyway the following code now works, so thanks for your help!


Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Union(Range("G8:GE30"), Range("G35:GH57"))) Is Nothing Then
    Select Case Target.Value
        Case "L"
            icolor = 16
        Case "l"
            icolor = 16
        Case "B"
            icolor = 5
        Case "b"
            icolor = 5
        Case 0 To 1
            icolor = 36
        Case Else
            'Whatever
    End Select
    Target.Interior.ColorIndex = icolor
End If
End Sub

Thanks

Thread: Conditional Formatting

Thread Tools

Display

Conditional Formatting

Conditional formatting

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions