Conditional formatting limits

[Lucas Lehmer]

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

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Lucas Lehmer]

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!
So, the formula "=A1" only copies the VALUE but not the FORMAT.
How come?
And is there a solution for this problem?

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Lucas Lehmer]

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.

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Lucas Lehmer]

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]

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.

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Lucas Lehmer]

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