"Conditional formatting" via VB?

[Trista]

I created a workbook which uses about 3000 cells that have
conditional formatting.

Basically, the cell uses a dropdown and if "A" is chosen,
the cell turns orange, "B" makes it green, "C" makes it
blue. Problem is that when I go to save it, I receive the
message that "Excel could not save all the data and
formatting you recently added to filename.xls" because
excel will not save files with conditional formatting more
than 2050 rows.

So I'm trying to come up with another way to do this. Is
there a visual basic trick to doing so? Unfortunately, I
am not that skilled at vb...

Thanks.

 

[Tod]

You can use the Worksheet Change event.

For example,

'Type A, B or C anywhere on the sheet
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "A" Then
Target.Interior.ColorIndex = 44
ElseIf Target.Value = "B" Then
Target.Interior.ColorIndex = 10
ElseIf Target.Value = "C" Then
Target.Interior.ColorIndex = 5
End If
End Sub

To limit the event to certain cells, try something like:

'Type A, B or C anywhere on the sheet
Private Sub Worksheet_Change(ByVal Target As Range)
Set InRange = Intersect(ActiveSheet.Range("A1:A100"),
Target)
If Not InRange is Nothing then
If Target.Value = "A" Then
Target.Interior.ColorIndex = 44
ElseIf Target.Value = "B" Then
Target.Interior.ColorIndex = 10
ElseIf Target.Value = "C" Then
Target.Interior.ColorIndex = 5
End If
end if
End Sub

 

[Trista]

Thank you - works perfectly!