Conditional formatting limits
 

Hello everyone,

Currently I am working on a spreadsheet with the property that every
cell can take the values 1 through 9.
Now I would like these cell to automatically take another background
color depending on these values (1=yellow ,2=green ,..., 9=black).

My first idea was to solve this problem via conditional formatting, but
apparently Excel does not allow more than 3 conditions, and I need 9 of
them!

Does anyone know a workaround this problem?
Many thanks in advance!
Lucas.


--
Lucas Lehmer
------------------------------------------------------------------------
Lucas Lehmer's Profile: http://www.excelforum.com/member.php...o&userid=34514
View this thread: http://www.excelforum.com/showthread...hreadid=542791

Conditional formatting limits
 

Worksheet events. For example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
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.


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Lucas Lehmer"
wrote in message
news:Lucas.Lehmer.27xuf3_1147846636.6559@excelforu m-nospam.com...

Hello everyone,

Currently I am working on a spreadsheet with the property that every
cell can take the values 1 through 9.
Now I would like these cell to automatically take another background
color depending on these values (1=yellow ,2=green ,..., 9=black).

My first idea was to solve this problem via conditional formatting, but
apparently Excel does not allow more than 3 conditions, and I need 9 of
them!

Does anyone know a workaround this problem?
Many thanks in advance!
Lucas.


--
Lucas Lehmer
------------------------------------------------------------------------
Lucas Lehmer's Profile:
http://www.excelforum.com/member.php...o&userid=34514
View this thread: http://www.excelforum.com/showthread...hreadid=542791

Conditional formatting limits
 

Thank you, Bob!

It really works!
However, I still have a small problem...
Let's say in cell A1 I can enter any value between 1 and 9.
And cell A2 contains the formula "=A1".

Well, in that case, if I enter 3 in cell A1, the cell A1 turns red, but
A2 doesn't!
How come?
And is there a solution for this problem?


--
Lucas Lehmer
------------------------------------------------------------------------
Lucas Lehmer's Profile: http://www.excelforum.com/member.php...o&userid=34514
View this thread: http://www.excelforum.com/showthread...hreadid=542791

Conditional formatting limits
 

Try this variation

Private Const WS_RANGE As String = "A1:A2"

'-----------------------------------------------------------------
Private Sub Worksheet_Calculate()
'-----------------------------------------------------------------
Dim cell As Range
For Each cell In Me.Range(WS_RANGE)
SetColour cell
Next cell
End Sub

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
SetColour Target
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub SetColour(ByVal Target As Range)
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
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.



--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Lucas Lehmer"
wrote in message
news:Lucas.Lehmer.27y6wz_1147862704.9114@excelforu m-nospam.com...

Thank you, Bob!

It really works!
However, I still have a small problem...
Let's say in cell A1 I can enter any value between 1 and 9.
And cell A2 contains the formula "=A1".

Well, in that case, if I enter 3 in cell A1, the cell A1 turns red, but
A2 doesn't!
How come?
And is there a solution for this problem?


--
Lucas Lehmer
------------------------------------------------------------------------
Lucas Lehmer's Profile:
http://www.excelforum.com/member.php...o&userid=34514
View this thread: http://www.excelforum.com/showthread...hreadid=542791

Conditional formatting limits
 

Thank you for the quick reply, Bob.

But unfortunately, no luck!
The effect is the same: only the VALUE is copied from A1 to A2, but not
the FORMAT :-(((

Did you try it yourself? And did it work?
In that case I might me doing something wrong.

Regards, Lucas.


--
Lucas Lehmer
------------------------------------------------------------------------
Lucas Lehmer's Profile: http://www.excelforum.com/member.php...o&userid=34514
View this thread: http://www.excelforum.com/showthread...hreadid=542791

Conditional formatting limits
 

I did try it Lucas, but I am not sure what you mean by the value being
copied but not the format. My code doesn't copy anything, it just sets the
background colour of those cells.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Lucas Lehmer"
wrote in message
news:Lucas.Lehmer.27y9xc_1147866601.9027@excelforu m-nospam.com...

Thank you for the quick reply, Bob.

But unfortunately, no luck!
The effect is the same: only the VALUE is copied from A1 to A2, but not
the FORMAT :-(((

Did you try it yourself? And did it work?
In that case I might me doing something wrong.

Regards, Lucas.


--
Lucas Lehmer
------------------------------------------------------------------------
Lucas Lehmer's Profile:
http://www.excelforum.com/member.php...o&userid=34514
View this thread: http://www.excelforum.com/showthread...hreadid=542791

Conditional formatting limits
 

Ok Bob,

You solution DOES indeed work !!!!
I made a really silly mistake, by entering the formule "=A1" in cell B1
in stead of A2. Of course it didn't work that way!

Thanks again, you made my day!
Kind regards,
Lucas.


--
Lucas Lehmer
------------------------------------------------------------------------
Lucas Lehmer's Profile: http://www.excelforum.com/member.php...o&userid=34514
View this thread: http://www.excelforum.com/showthread...hreadid=542791

Conditional formatting limits
 

Hmm, still another question.

How could I change the code in the VB-macro to have the color of the
cell value in white when the background color is blue or black?
Or, put otherwise, I would like the color of the cell value to change
into white when the value of the cell is (in my case) 7 or 8.

Can this be accomplished?
Lucas.


--
Lucas Lehmer
------------------------------------------------------------------------
Lucas Lehmer's Profile: http://www.excelforum.com/member.php...o&userid=34514
View this thread: http://www.excelforum.com/showthread...hreadid=542791

Conditional formatting limits
 

I haven't seen your code but this should get you started

Private Sub SetColour(ByVal Target As Range)
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
Case 7:
.Interior.ColorIndex = 1
.Font.ColorIndex = 2
End Select
End With
End Sub


--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Lucas Lehmer"
wrote in message
...

Hmm, still another question.

How could I change the code in the VB-macro to have the color of the
cell value in white when the background color is blue or black?
Or, put otherwise, I would like the color of the cell value to change
into white when the value of the cell is (in my case) 7 or 8.

Can this be accomplished?
Lucas.


--
Lucas Lehmer
------------------------------------------------------------------------
Lucas Lehmer's Profile:
http://www.excelforum.com/member.php...o&userid=34514
View this thread: http://www.excelforum.com/showthread...hreadid=542791

Conditional formatting limits
 

Well Bob,

I take my hat off, you're a genius!
Thanks again for the support!

Kind regards,
Lucas.

PS: I still have another question (a difficult one I guess), but it can
wait until tomorrow. But probably unsolvable in Excel anyway...


--
Lucas Lehmer
------------------------------------------------------------------------
Lucas Lehmer's Profile: http://www.excelforum.com/member.php...o&userid=34514
View this thread: http://www.excelforum.com/showthread...hreadid=542791