RE: Conditional Formatting

Hi Lisa,

This scenario is slightly diffrent becuase of the fixed cell range (a fixed target), which means you can use the FOR structure. The previous scenario will wait for a new data entry to trigger the colour change.

Here is the sample codes:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub ConditionalFormat()
Dim Counter As Integer
Dim Row As Integer 'The row being copied
Dim TotalRows As Integer 'Number of Colour Entries

Row = 2

Sheets("Sheet1").Select
Range("E6").Select

TotalRows = Range("E6").CurrentRegion.Rows.count
Debug.Print TotalRows

'Red
For Counter = 1 To TotalRows
If Cells(Row, 5).Value = "Red" Then
With Cells(Row, 5)
.Interior.ColorIndex = 3
.Font.Bold = True
.Font.ColorIndex = 3
End With

'Orange
ElseIf Cells(Row, 5).Value = "Orange" Then
With Cells(Row, 5)
.Interior.ColorIndex = 46
.Font.Bold = True
.Font.ColorIndex = 46
End With

'Yellow
ElseIf Cells(Row, 5).Value = "Yellow" Then
With Cells(Row, 5)
.Interior.ColorIndex = 6
.Font.Bold = True
.Font.ColorIndex = 6
End With

'Light Green
ElseIf Cells(Row, 5).Value = "Light Green" Then
With Cells(Row, 5)
.Interior.ColorIndex = 35
.Font.Bold = True
.Font.ColorIndex = 35
End With

'Dark Green
ElseIf Cells(Row, 5).Value = "Dark Green" Then
With Cells(Row, 5)
.Interior.ColorIndex = 10
.Font.Bold = True
.Font.ColorIndex = 10
End With
End If

Row = Row + 1 'Moves to next row after formatting
Next Counter


End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

See if this helps.

Katie
Microsoft Certified Trainer