6 conditional formats in Excel 2002

  • Thread starter Thread starter Tracey
  • Start date Start date
How are the values derived?

If formula-derived you would want a calculate event.

Private Sub Worksheet_Calculate()
Dim Target As Range
For Each Target In Me.Range("A1:A100")
With Target
Select Case .Value
Case Is = 3: .Interior.ColorIndex = 7
Case Is = 1: .Interior.ColorIndex = 10
Case Is = 2: .Interior.ColorIndex = 16
Case Is = 4: .Interior.ColorIndex = 4
Case Is = 5: .Interior.ColorIndex = 6
Case Is = 0: .Interior.ColorIndex = 0
'etc.
End Select
End With
Next Target
End Sub

If something else, post back.


Gord Dibben MS Excel MVP
 
Thanks for the reply Gord - my real question is a bit more complex...

I really need to know if it's possible to CF a cell on one worksheet based
on a value on a 2nd worksheet. To complicate it further, I have a total of 6
formats that I would like to apply.
 
More details please.

Use sheet names and cell references rather than generic terms like "cell".

Yes, you can CF a cell on sheet1 based upon a value in a cell on sheet2.

You just have to give the value cell a defined name.

But with 6 formats you will need VBA or if the values are numeric you can
get up to 6 without VBA

See John McGimpsey's site for details of that.

http://www.mcgimpsey.com/excel/conditional6.html


Gord
 
One detail would be.............how are the values on sheet2 input?

Calculated or manually?


Gord
 
I have 4 sheets that detail a group of Stores and their Personnel by their
Jobs and the Performance rating for last year and a fifth sheet that
summaries all of this data

e.g.
Sheet 1 = Store 1

Col A Col B Col C
Manager Bob High Potential
Meat Mgr Carol High Value
Bake Mgr Ted Performance Manage

Sheet 2 = Store 2 etc

Sheet 5 summarises all of the other 4 to give a complete list for the region
via a link to each individual store sheet
e.g.

Row Col A Col B Col C Col D Col E
Store 1 Store 2 Store 3 Store 4
Row 2 Manager Bob Alice Goofy Daisy
Row 3 Meat Mgr Carol Mickey Clarabelle Tracey
Row 4 Bake Mgr Ted Minnie Donald etc

I need to format cells B2 through E4 based on their Performance rating in
Col C from their Store sheet.

High Potential = Blue
High Value = Yellow
Performance Manage = Red
Promotable Next Lvl = Green
Promotable Current = Light Green
Too Soon to call = Blank

I hope this makes sense... thanks for your time Gord.
 
Looks a little too complex for my skills.

Hopefully someone else can give assistance.

Your first post looked easy enough but as you say, that was not your real
question.

Apologies for wasting your time.


Gord
 
Try placing this in the sheet code module
Untested, but should work...

Tim


Private Sub Worksheet_Activate()
UpdateFormats
End Sub

Sub UpdateFormats()
Dim c As Range, rng As Range
Dim Store, Pos, EmpName, Rating
Dim cIndex As Integer

'loop through each cell which needs formatting
For Each c In Me.Range("B2:E4").Cells

Store = c.EntireColumn.Cells(1).Value
Pos = c.EntireRow.Cells(1).Value
EmpName = c.Value
Rating = ""
cIndex = xlNone
'look for the employee in the relevant sheet
'(assumed sheets named "Store 1","Store 2" etc)
Set rng = ThisWorkbook.Sheets(Store).Cells(1)
Do While Len(rng.Value) > 0
If rng.Value = Pos And rng.Offset(0, 1).Value = EmpName
Then
Rating = rng.Offset(0, 2).Value
Exit Do
End If
Set rng = rng.Offset(1, 0)
Loop

If Rating <> "" Then
Select Case Rating
Case "High Potential": cIndex = 1
Case "High Value": cIndex = 2
'etc etc
End Select

End If

c.Interior.ColorIndex = cIndex

Next c
End Sub
 
Hi Tim,

Thanks for your code but I'm getting an out of subscript error at the
following line:-
Set rng = ThisWorkbook.Sheets(Store).Cells(1)

Any suggestions?
 
Sorry Tim - I've corrected the out of range error and the code is running ok
but it's not actually changing anything??
 
If it's not changing anything then it hasn't found a match on the "store x"
sheet,
or it's not getting triggered.

Try adding this:

Debug.Print Store, Pos, EmpName, cIndex

Immediately before the "Next c" line.

Or you can send me your workbook
tim j williams (no spaces)
at gmail dot com

Tim
 
Also, I forgot to add a check on the cell being formatted, to make
sure it had a value before looking it up on the other sheets.

For Each c In Me.Range("B2:E4").Cells
If Len(c.Value) > 0 Then
'...rest of code...

End If
Next c

Tim
 
Back
Top