Conditional Formatting Again

[Jack_Daw]

Sorry if this has been asked before, I want a cell colour to change if I put 4 words in (was ok when I only had three!). If I put say Bob, Paul, Bill or Mark in I want it to change colour, can this be done with CF?

Cheers

[Bryan Hessey]

Sorry if this has been asked before, I want a cell colour to change if I put 4 words in (was ok when I only had three!). If I put say Bob, Paul, Bill or Mark in I want it to change colour, can this be done with CF?

Cheers

Hi, just a few times, in your sheet, rightmouse the tab and View Code, copy and paste this code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iColour As Integer
Dim iRow As long, iColumn As Integer
    If Not Intersect(Target, Range("A:B")) Is Nothing Then
        iColour = 0
        iRow = Target.Row
        iColumn = Target.Column
            If Cells(iRow, iColumn).Value = "John" Then
            iColour = 4
            ElseIf Cells(iRow, iColumn).Value = "Jack" Then
            iColour = 5
            ElseIf Cells(iRow, iColumn).Value = "Fred" Then
            iColour = 6
            ElseIf Cells(iRow, iColumn).Value = "Joan" Then
            iColour = 45
            ElseIf Cells(iRow, iColumn).Value = "Mary" Then
            iColour = 3
            ElseIf Cells(iRow, iColumn).Value = "Jean" Then
            iColour = 1
        End If
        Cells(iRow, iColumn).Interior.ColorIndex = iColour
     End If
End Sub

and amend the names and colours to suit.

hth
---

[Jack_Daw]

Great thanks for that, found a quick cheat, the cell next to it has a date in it if I use any of the names. So I just changed the names cell to fill in if the date cell was greater than 0.

Will use yours though as has far greater use and flexibility

Thanks a lot for your quick answer

[Bryan Hessey]

Great thanks for that, found a quick cheat, the cell next to it has a date in it if I use any of the names. So I just changed the names cell to fill in if the date cell was greater than 0.

Will use yours though as has far greater use and flexibility

Thanks a lot for your quick answer

good to see the 'cheat'
and thanks for the response.
---