Conditional format from a reference cell?

[oopsie poopsie]

I have created an item list for one column in my spreadsheet and would
like to color the row based on the value in that column. I have more
than three states to choose from and would like to color code them
based on the state, so the traditional conditional formatting won't
work for me.

Is there a way to format cells using a reference cell as the template?

Example:

Using LOOKUP for the value of column A referencing columns X and Y

Column X:
1_idle
2_walk
3_trot
4_run
5_gallop

Column Y:
0% (colored green)
25% (colored yellow)
50% (colored orange)
75% (colored red)
100% (colored magenta)

I'd like columns A:F to take on the color of column Y based on the
LOOKUP of column X.

Any advice?

 

[Norman Jones]

Hi Oopsie,

Try:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim rcell As Range
Dim iColor As Long

Set Rng = Intersect(Columns("A"), Target)

If Not Rng Is Nothing Then
For Each rcell In Rng.Cells
With rcell
Select Case LCase(.Value)
Case "idle": iColor = 4
Case "walk": iColor = 6
Case "trot": iColor = 45
Case "run": iColor = 3
Case "gallop": iColor = 26
Case Else: iColor = xlNone
End Select
.Resize(1, 6).Interior.ColorIndex = iColor
End With
Next rcell
End If

End Sub
'<<=============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.

 

[Norman Jones]

Hi Oopsie,

I should add that if you are not familiar with macros, you may wish to visit
David McRitchie's 'Getting Started With Macros And User Defined Functions'
at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Peo Sjoblom]

It's not possible, you can setup conditional formatting for 3 different
conditions but you can never have a formula copy over the formats

--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon

 

[Norman Jones]

Hi Oopsie,

You may also wish to look at xlDynamic's CFPlus addin which is available
free of charge at:

http://www.xldynamic.com/source/xld.CFPlus.Download.html