Hi Guys. I am trying to get the cells of a selected area to change font & background colour based on a cell value. I can’t use conditional formatting because there are more than 3 variants, so I want it to work when a save button is clicked. So far I have this:
Sub Colorchange()
For rwIndex = 5 To 17
For colIndex = 5 To 35
With Worksheets(“Sheet1”).Cells(rwIndex, colIndex)
If .Value = HH Then
.Font.Color = RGB(255, 0, 0)
.Interior.Color = RGB(0, 255, 0)
Else:
.Font.Color = RGB(0, 0, 0)
.Interior.Color = RGB(255, 255, 255)
End If
End With
Next colIndex
Next rwIndex
End Sub
All this does is change the blank cells to green and leaves the cells with values in them as they are. Any ideas where I’m going wrong?
/edit The final document is going to be shared so it cannot be a private sub, sheet by range either.
That first one works thanks Greg, but it seems that it does not like letters without quotes around them (I forgot to mention that the cells contain letters not numbers :doh:). All I have to do now is replicate it for the whole document and the jobs a good’un.
For refernce the whole script goes:
Sub CellColours()
Dim i As Integer
Dim j As Integer
For i = 5 To 15
For j = 4 To 17
Cells(j, i).Select
If Selection.Value = "H" Then
Selection.Font.Color = RGB(0, 0, 0)
Selection.Interior.Color = RGB(0, 255, 0)
ElseIf Selection.Value = "HH" Then
Selection.Font.Color = RGB(0, 0, 0)
Selection.Interior.Color = RGB(0, 255, 0)
ElseIf Selection.Value = "S" Then
Selection.Font.Color = RGB(0, 0, 0)
Selection.Interior.Color = RGB(255, 255, 0)
Else
Selection.Font.Color = RGB(0, 0, 0)
Selection.Interior.Color = RGB(255, 255, 255)
End If
Next j
Next i
End Sub
The only real changes were to the cell value and using standard RGB refs instead of colourindex.
The only other thing to remember is that if the sheet you are changing the colours on will be protected, remember to select the “Format Cells” check box under the Protect Sheet option or the macro will not run.
