6 conditional formats in Excel 2002

G

Gord Dibben

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
 
T

Tracey

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.
 
G

Gord Dibben

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
 
G

Gord Dibben

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

Calculated or manually?


Gord
 
T

Tracey

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.
 
G

Gord Dibben

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
 
T

Tim Williams

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
 
T

Tracey

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?
 
T

Tracey

Sorry Tim - I've corrected the out of range error and the code is running ok
but it's not actually changing anything??
 
T

Tim Williams

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
 
T

Tim Williams

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
 
Top