Excel 2k - Unlimited Conditional Formatting?

[Mictain]

Hi All,

I'm trying to implement a change in cell colour by conditionally formatting a grid of cells using the following:

Cell Value Is Equal To "X" - background = green
Cell Value Is Equal To "Y" - background = red
Etc.

However, it seems I can only apply three conditions like this, but I have ELEVEN possible values to work with.

Any ideas how I might be able to get this plan to work, please?

Many thanks,

Neil.

 

[SkipVought]

Neil,

The limit is 3. You can write some VBA code to format more than 3, but it will not function "automatically" like the built-in conditional formatting (ie changing upon entry) UNLESS you call CondFmt from the Worksheet_Change event for the range you want to format...

Sub CondFmt()
Dim vColorIndex
With ActiveCell
Select Case .Value
Case 1
vColorIndex = 11
Case 2
vColorIndex = 22
Case 3
vColorIndex = 33
Case 4
vColorIndex = 44
Case Else
vColorIndex = 55
End Select
.Interior.ColorIndex = vColorIndex
End With
end sub
[/code Skip,
Skip@theofficeexperts.com

 

[Mictain]

Thanks, Skip. My VBA's pretty cack but I'll have a go anyway by pinching what you've already supplied and if I can't get it to go, the requestor will just have to go without!

Neil.

 

[Zathras]

This may be moot since you did not respond to Skip's post by saying you don't need more than two vColorIndex values, but just in case...

It is not clear to me exactly what you mean when you say you have eleven values.

In my mind that could mean any of at least four things:
(1) You have eleven possible values each of which needs its own color, or
(2) You have many (more than eleven) values each of which needs to be mapped to one of eleven colors, or
(3) You have eleven values each of which needs to be mapped to either red or green (or none), or
(4) You have many values each of which needs to be mapped to red or green (or none).

If it is (1) or (2), then you are indeed stuck with Skip's answer. However, if it is (3) then you CAN still use conditional formatting with suitable formulas. And, if it is (4) you MAY still be able to use conditional formatting with formulas depending on the logic of the color selection.

 

[Mictain]

Zathras,

Your thought number 1 is correct - eleven possible values needing their own colours.

As mentioned, my VBA experience is virtually nil, so my response would be muted by what I recognise and understand about the code. I need practice. Lots of it!

Thanks for replying.

Neil.