If range A = x/y/z colour adjacent cell

hi,

After some help with a macro.

I have a spreadsheet that tracks staff, in column F the value if either empty, "OK" or other

if a cell in column F1 -> Last Row is empty the cell to its left remains uncoloured, if it is "OK" the cell to the left is green, and if the cells is anything else then the cell to the left should be red.

it works with standard conditional formatting but everytime a new row is inserted it breaks the CF up into more than one rule and over time it gets very clogged up and sometimes breaks.

I've tried to get bits of code of the new to make this myself but im getting no where, so i would greatly appreciate it if someone could create a working macro that does this on the fly.

Much appreciated.

Originally Posted by Palmetto

Rather than a macro, try this first (on a back up copy)

1. Select all of the cells and clear the conditional formatting
2. Select all of the cells, again if needed, and re-apply your conditional formatting
3. Convert the data range into an Excel Table: Menu > Insert > Table ( you should be using a header row )

The Table will automatically extend all formatting and formulas to new rows and you shouldn't encounter the issue you described above.

Done step by step and its still breaking the CF up, also one thing this does mess up is the cell merge, columns f,g,h are all merged in that range.

Ok, ive found some code online that does exactly what i need: http://www.rondebruin.nl/find.htm

ive managed to modify to the code to find what i want and turn the adjacent cell green, but how can i get it to do the check for an empty cell and to check if its anything other than empty or "OK"

    '####################################
    'Check for Rows that are equal to OK
    '####################################
    
    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim FirstAddress As String
    Dim MySearch As Variant
    Dim myColor As Variant
    Dim Rng As Range
    Dim I As Long

    'Fill in the search Value and color Index
    MySearch = Array("OK")
    myColor = Array("4")

    'You can also use more values in the Array
    'MySearch = Array("ron", "jelle", "judith")
    'myColor = Array("3", "6", "10")


    'Fill in the Search range, for the whole sheet use
    'you can use Sheets("Sheet1").Cells
    With Sheets("JAN").Range("F18:H100")

        'Change the fill color to "no fill" in all cells
        Interior.ColorIndex = xlColorIndexNone

        For I = LBound(MySearch) To UBound(MySearch)

            'If you want to find a part of the rng.value then use xlPart
            'if you use LookIn:=xlValues it will also work with a
            'formula cell that evaluates to MySearch(I)
            Set Rng = .Find(What:=MySearch(I), _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlFormulas, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)

            If Not Rng Is Nothing Then
                FirstAddress = Rng.Address
                Do
                    Rng.Offset(0, -1).Interior.ColorIndex = myColor(I)
                    Set Rng = .FindNext(Rng)
                Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
            End If
        Next I
    End With
End Sub