Using VBA for conditional Formatting

[bjfreeman]

I need to apply 8 formatting conditions to a list of approx 10,000 entries.(Each entry is a bin location in a racked warehouse)
Each entry consists of 8 digits of numeric text eg:
In sequence 03690300,
03710300,
03710301,
03010400.

I am currently using to following wildcard formula,=COUNTIF(T2,""????03*""), but this is restricting me to the Excel 2003 limit of 3 conditions.

Also is it possible to apply 2 different conditions to a single entry, as i want to also highlight certain areas due to weight restrictions.

Any help would be greatly appreciated.

[Bob Phillips]

An example

[vba]

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "T2:T20" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case True
Case .Value Like "????03*": .Interior.ColorIndex = 3 'red
Case .Value Like "????04*": .Interior.ColorIndex = 6 'yellow
Case .Value Like "*A*": .Interior.ColorIndex = 5 'blue
Case .Value Like "?Z?": .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[bjfreeman]

Thanks xld

But unfortunately the data is generated by SAP onto a new temporary spreadsheet, Worksheet in Basis(1),(2) etc depending on what reports are running. If i try to copy the values to a workbook with the code saved into the sheets, no changes take place, and i get a trace error of Number formatted as text. Only double-clicking on each individual cell 'activates' the code.

Could there be any other solutions?