Conditional formatting code please

**Cath** · 08-21-2007, 04:57 AM

I have run out of conditonal formatting rules in my spreadsheet and I just need one more that I imagine will need to be coded.......

If cell H1 is greater than 1 then colour G1 purple (or thereabouts)

Can someone help please before I have to shell out for a 2007 licence

Many thanks

**Trebor76** · 08-21-2007, 05:09 AM

Hi Cath,

You can actually have four criteria in that if you shade the relevant range in the colour you want (purple) as the fourth (last) criteria, then this colour will be returned (defaulted) if the first three criteria aren't met.

Beyond this, have a look at this code.

HTH

Robert

**Cath** · 08-21-2007, 06:01 AM

Forgive me, but I am not overly good at coding....
It seems I can colour cells in a specific range if the cell the colour is dependant upon is in that range, but what I want to do is colour the cell next to the one that's value is between 2 To 5

What I've got so far, which isn't enough:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

    If Not Intersect(Target, Range("offers")) Is Nothing Then
        Select Case Target
            Case 2 To 5
                icolor = 55
          
        End Select
        
        Target.Interior.ColorIndex = icolor
    End If

End Sub

The cell I want to colour is the range "offers" but the cell the colour is dependant upon (i.e. the value 2 To 5) is in a different range....

Does that make sense?

**Trebor76** · 08-21-2007, 06:34 PM

Hi Cath,

Any cell can have a conditional format that is reliant on the value from any another cell. Have a look at the attached screen shot where this conditional format will work on any cell based on the value in B1.

You really only need to use code if you have more than four formats.

HTH

Robert