PDA

View Full Version : Solved: excel 2003 conditional formatting



tkaplan
03-18-2011, 09:49 AM
I have an excel 2007 document where I have 9 conditional formats on the cells. My coworkers need to be able to open up the file but they are all using 2003 so it gives them an error. Is there VBA that I can write somehow that will allow more than 3 conditional formats in 2003?

Bob Phillips
03-18-2011, 10:13 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H5" '<<<< change to suit

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

Select Case Target.Value

Case 1: 'do something
Case 2: 'do something else
Case 3: 'do something else again
'etc
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


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.

tkaplan
03-18-2011, 11:18 AM
ok. I'm feeling stupid. what kind of code would I be putting by each case? for example say I want that if the value of the cell is less than 5, font should be bold. How would I put that in?

Bob Phillips
03-18-2011, 11:25 AM
Case <5: Target.Font.Bold = True

tkaplan
03-18-2011, 11:30 AM
ok so now 2 more things: can you make it multiple formats (i.e. change font color and background color) and also, if I'm using a formula to determine the condition (i.e. I am looking at the 2 left characters of the cell and if they equal "WA" then it's one format, XB is another, etc.

thanks

Bob Phillips
03-18-2011, 11:37 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H5" '<<<< 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 "WA*" :
.Font.Bold = True
.Interior.Colorindex = 37

Case .Value Like "XB*" :
.Font.Colorindex = 3 'red
.Font.Bold = True
.Interior.Colorindex = 39
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

tkaplan
03-18-2011, 12:11 PM
perfect! thanks :)

In 2007 you can format cells with gradient fills. In 2003 I only see the regular patterns. Am I missing something or is it just not available in 2003? (I need to get my coworkers all upgraded to 2007!)

Bob Phillips
03-18-2011, 12:23 PM
Don't do it. Skip 2007 and go straight to 2010. 2007 is a cr#p release.

tkaplan
03-21-2011, 06:31 AM
I haven't worked on 2010 yet. good to know though! thanks for all of your help