Hi,

I'm trying to create a Macro which will change the interior colour of 3 cells based on the value of another cell. I can't use Conditional Formatting as our Sales force use Office 2003 and therefore I can only have 3(4) different rules however I need 5. I have tried many attempts and scoured the net however have had no luck so far. Here is my best attempt at a Macro so far:


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B1").Value = 1 Then
    
    Cells.Find(What:="baaa", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 8).Range("A1:A3").Select
    Selection.Interior.ColorIndex = 37
    
    End If
End Sub

You'll be able to see I am searching for a independent text string "baaa" and then once found offsetting by 8 cells to the right. I use independent text strings so that if somebody inserts a row it will still find and offset to the correct cell.

Does anyone see anything wrong with the above macro as it simply isn't working!

Kind Regards,
Harry Seager