Hi,

I have searched this forum (and others) for the solution to this problem but without success. I hope somebody can help.

I have been using conditional formatting for a project in Excel 2007 but as the end users are using Excel 2003, I have had to switch to the following VBA solution as my requirements exceed the standard 3 available conditions. I have looked at using custom formatting but I need to format the cell colour rather than just the font colour.

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

    If Not Intersect(Target, Range("C19:IV384")) Is Nothing Then
        Select Case Target
            Case "0.5", "1", "U"
                icolor = 38
            Case "C", "M", "P"
                icolor = 40
            Case "A", "S", "D"
                icolor = 36
            Case "L", "UP", "C/E"
                icolor = 35
            Case Else
                'Whatever
        End Select
        
        Target.Interior.ColorIndex = icolor
    End If

End Sub
I am hopeful somebody can help me modify this VBA to achieve the following:

* When the macro is run on one worksheet, formatting and values are replicated simultaneously on another identical worksheet (not necessarily vice versa).

* As well as formatting cell colour when containing a value, a border should also be added with different colours for the top, bottom, left and right border.

* When the cell contains no value, the borders should return to how they were previously.

Many thanks in advance for any advice or solutions.