Hi All,

Is it possible to create VBA code for 6 conditional formats in Excel 2002?

--
Tracey @ BrisVegas

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

On Wed, 13 Jan 2010 15:04:01 -0800, Tracey
wrote:

Hi All,

Is it possible to create VBA code for 6 conditional formats in Excel 2002?

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.

--
Tracey @ BrisVegas

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

On Wed, 13 Jan 2010 16:15:01 -0800, Tracey
wrote:

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.

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

Calculated or manually?


Gord

On Wed, 13 Jan 2010 16:47:13 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Wed, 13 Jan 2010 16:15:01 -0800, Tracey
wrote:

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.

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.


--
Tracey @ BrisVegas

The first 4 sheets are all entered manually
--
Tracey @ BrisVegas


"Gord Dibben" wrote:

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

Calculated or manually?


Gord

On Wed, 13 Jan 2010 16:47:13 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Wed, 13 Jan 2010 16:15:01 -0800, Tracey
wrote:

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.

.

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


On Wed, 13 Jan 2010 17:53:01 -0800, Tracey
wrote:

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.

Thanks for your time anyway Gord
--
Tracey @ BrisVegas


"Gord Dibben" wrote:

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


On Wed, 13 Jan 2010 17:53:01 -0800, Tracey
wrote:

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.

.

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




On Jan 13, 5:53*pm, Tracey wrote:
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.

--
Tracey @ BrisVegas

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?
--
Tracey @ BrisVegas


"Tim Williams" wrote:


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




On Jan 13, 5:53 pm, Tracey wrote:
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.

--
Tracey @ BrisVegas

.

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


"Tracey" wrote:

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?
--
Tracey @ BrisVegas


"Tim Williams" wrote:


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




On Jan 13, 5:53 pm, Tracey wrote:
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.

--
Tracey @ BrisVegas

.

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

"Tracey" wrote in message
...
Sorry Tim - I've corrected the out of range error and the code is running
ok
but it's not actually changing anything??
--
Tracey @ BrisVegas


"Tracey" wrote:

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?
--
Tracey @ BrisVegas


"Tim Williams" wrote:


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




On Jan 13, 5:53 pm, Tracey wrote:
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.

--
Tracey @ BrisVegas

.

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


On Jan 14, 8:14*pm, "Tim Williams" wrote:
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

"Tracey" wrote in message

...



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

"Tracey" wrote:

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?
--
Tracey @ BrisVegas

"Tim Williams" wrote:

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

On Jan 13, 5:53 pm, Tracey wrote:
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.

--
Tracey @ BrisVegas

.- Hide quoted text -

- Show quoted text -