Conditional Formatting

[John Calder]

Hi

I run Excel 2K

Conditional Formatting in Excel 2K only allows three conditions to be
applied. I need 8 conditions.

What I have is 8 check boxes that indictate TRUE or FALSE depending on
wheather the check box had been ticked or not.

Check Box 1 linked to cell IN2
Check Box 2 linked to cell IO2
Check Box 3 linked to cell IP2
Check Box 4 linked to cell IQ2
Check Box 5 linked to cell IR2
Check Box 6 linked to cell IS2
Check Box 7 linked to cell IT2
Check Box 8 linked to cell IU2

I would like to nominate a cell (say U2) that changes colour depending on
which of the 8 check boxes is ticked.

Is there a way of doing this?

Thanks

 

[Max]

I'd use a simpler way to just return the selection made as a value
Assume your array of 8 contiguous horiz cells is in A1:H1
where there will only be a single TRUE at any time
In say, A2:
=IF(ISNA(MATCH(TRUE,A1:H1,0)),"",MATCH(TRUE,A1:H1,0))
will return a relative number denoting which cell within A1:H1 contains the
TRUE, eg: 2 = B1, 5 = E1, and so on. Any worth? hit the YES below

 

[John Calder]

Max

The person I am doing this for is really keen to have colour as the
identifier but if all else fails I will definately look at your option.


Thanks

 

[Max]

With cell A2 housing the earlier formula returning the numbers 1 - 8, think
you could try tinkering with the sheet sub below to fill-color A2, with the
font color for A2 set to match the fill color so as to mask the underlying
number returned by the formula

To install:
Copy the sub
Right-click on the sheet tab > View Code
Paste the sub into the code window on the right
Press Alt + Q to get back to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer, fcolor As Integer
Set Target = [A2]
Select Case Target
Case 1
icolor = 6
fcolor = 6
Case 2
icolor = 12
fcolor = 12
Case 3
icolor = 7
fcolor = 7
Case 4
icolor = 53
fcolor = 53
Case 5
icolor = 15
fcolor = 15
Case 6
icolor = 42
fcolor = 42
Case 7
icolor = 50
fcolor = 50
Case 8
icolor = 13
fcolor = 13
Case Else
End Select

With Target
.Interior.ColorIndex = icolor
.Font.ColorIndex = fcolor
End With

End Sub

 

[John Calder]

Max

Thanks again for your help. I have no problem getting the 1st part to work
but the VB does not work for me. At best I got it to change to one colour
(yellow) but it would not change to any other colour.

The cells which I need to colour have a $ value input.

Example

U2 = $150
U3 = $300
U3 = $0
U4 = $49

etc etc

These are the cells I need to change colour. (A different colour represents
a different person)


The cells which contain your folrmula are

IM2 =IF(ISNA(MATCH(TRUE,IN2:IU2,0)),"",MATCH(TRUE,IN2:IU2,0))
IM3 =IF(ISNA(MATCH(TRUE,IN3:IU3,0)),"",MATCH(TRUE,IN3:IU3,0))
IM4 =IF(ISNA(MATCH(TRUE,IN4:IU4,0)),"",MATCH(TRUE,IN4:IU4,0))

etc etc (This works OK)

So, essentially I need something that looks at your forlmula (in cells IM)
and changes the colours in column "U" based on the return from your formula.

I hope this helps.

Thanks

With cell A2 housing the earlier formula returning the numbers 1 - 8, think
you could try tinkering with the sheet sub below to fill-color A2, with the
font color for A2 set to match the fill color so as to mask the underlying
number returned by the formula

To install:
Copy the sub
Right-click on the sheet tab > View Code
Paste the sub into the code window on the right
Press Alt + Q to get back to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer, fcolor As Integer
Set Target = [A2]
Select Case Target
Case 1
icolor = 6
fcolor = 6
Case 2
icolor = 12
fcolor = 12
Case 3
icolor = 7
fcolor = 7
Case 4
icolor = 53
fcolor = 53
Case 5
icolor = 15
fcolor = 15
Case 6
icolor = 42
fcolor = 42
Case 7
icolor = 50
fcolor = 50
Case 8
icolor = 13
fcolor = 13
Case Else
End Select

With Target
.Interior.ColorIndex = icolor
.Font.ColorIndex = fcolor
End With

End Sub

--
Max
Singapore
---

Max

The person I am doing this for is really keen to have colour as the
identifier but if all else fails I will definately look at your option

 

[Max]

John, adapt the sub below (courtesy of a reply by MVP Jeff in another forum)
to suit the range to be colored, depending on the numbers returned by the
formulas in that range. As-is, it presumes that the range you want colored is
IM2:IM100, which contains formulas returning the numbers 1-8. I've tested it
and it works fine

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor, fcolor As Integer
Dim rng As Range
For Each rng In Range("IM2:IM100")
Select Case rng.Value
Case 1
icolor = 6
fcolor = 6
Case 2
icolor = 12
fcolor = 12
Case 3
icolor = 7
fcolor = 7
Case 4
icolor = 53
fcolor = 53
Case 5
icolor = 15
fcolor = 15
Case 6
icolor = 42
fcolor = 42
Case 7
icolor = 50
fcolor = 50
Case 8
icolor = 13
fcolor = 13
Case Else
End Select
With rng
.Interior.ColorIndex = icolor
.Font.ColorIndex = fcolor
End With
Next
End Sub

 

[John Calder]

Max

Once again thanks for your help. Your code does indeed work but not how I
intended it. The problem is that the cells I need coloured (U2:U51) also have
a manual input in each one. You solution as I understand it requires a
formula to be placed in each cell. Therfore I would not be able to place the
manual input that is required in each cell. The colour of each cell is not
dependant on the input of each cell it is dependant on the number that is
returned from your intial formula.

So, I have 8 tick boxes in cells C:J
Depending on which tick box I choose it returns a "TRUE" somewhere in the
range IN:IU
Your formula (in cell IM) returns the number in which the "TRUE" is returned.
What I need is that the corresponding cell in column U returns a colour
which represents the number returned by your formula. I can then place an
input (ie $300) into this cell.

eg:

1 = Yellow
2 = Red
3 = Pink
4 = Light Green
5 = Light Orange:
6 = Grey
7 = Light Blue
8 = Green

I hope I have managed to explain my situation a little better for you.

Thanks

 

[Max]

John,

Think its best that you start a new thread in this MS vba forum:
http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads

In your new post there, do mention your Excel version and ensure that
complete info is provided

MS has posted a msg that all xl newsgroups (like this one) will be closed in
end May 2010. As your query requires a vba solution, the forum above is
appropriate and you should be able to gather the expertise of vba responders
to get you going. The above forum is amongst those directed by MS for ng
posters, and is MS' way forward for queries wef Jun 2010. All the best to you
and farewell ..

 

[John Calder]

Max

You have been a huge help. Thanks a lot. I will repost as you suggested.

Thank You