Hi All

Just wondering if in 2003 you can have more than 3 conditional formats? If
not can it be done in VBA and what's the code. I'm good at copying and
pasting to adapt vba posts.

TIA
Bec

No, 2003 is limited to 3 conditions (giving 4 possible formats: default + 3).
You can do it in VBA, you use the worksheet's _Change() event to detect a
change in the cell(s) and generally use a Select Case statement to evaluate
the value of the cell, setting any number of formats based on those results.
Any code would go into the worksheet event module (right-click the sheet's
name tab and choose [View Code] from the list to get to the proper place).

You can record macros while setting up formats such as you want to get the
syntax needed to set up a particular format scenario to use within the
various Case Is portions of the Select Case block.

General code stub for a test, with several possible tests to see if the
change took place where you need to know. You can combine up tests to see if
it is in just about any kind of are you need to test.

Private Sub Worksheet_Change(ByVal Target As Range)
'code to detect change in one particular cell
'in this worksheet

If Target.Address < "$A$1" Then
'change not in cell A1, get out
Exit Sub
End If

'some other possibles testing
'but check out Application.Intersect also

'a specific column
'use the column letter and row 1 in it.
If Target.Column < Range("H" & 1).Column Then
'change didn't happen in column H
Exit Sub
End If

'a specific row
If Target.Row < 15 Then
'no change in row 15
Exit Sub
End If

'between 2 columns
If Target.Column < Range("C" & 1).Column Or _
Target.Column Range("F" & 1).Column Then
'no change in columns C, D, E or F
Exit Sub
End If

'general code to evaluate results
'showing different possible options
'testing stops with the first statement that
'evaluates as true
Select Case Target.Value
Case Is = 5
'formatting for when it is 5
Case Is = 10
'formatting when it's 10
Case Is 100
'formatting for when it goes over 100
Case Is = Range(B1).Value
'it matches value of B1 on same sheet
'note if it matches B1, and if B1 is
'negative, it stops here and doesn't
'go on to test the next Case
Case Is < 0
'when it's negative
Case Else
'all other conditions,
'probably set to "no special formatting"
End Select
End Sub




"Bec" wrote:

Hi All

Just wondering if in 2003 you can have more than 3 conditional formats? If
not can it be done in VBA and what's the code. I'm good at copying and
pasting to adapt vba posts.

TIA
Bec