If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Formatting
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 |
#2
|
|||
|
|||
Conditional Formatting
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 -- Max Singapore --- "John Calder" wrote: 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 |
#3
|
|||
|
|||
Conditional Formatting
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 "John Calder" wrote: 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 |
#4
|
|||
|
|||
Conditional Formatting
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 --- "John Calder" wrote: 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 |
#5
|
|||
|
|||
Conditional Formatting
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 "Max" wrote: 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 --- "John Calder" wrote: 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 |
#6
|
|||
|
|||
Conditional Formatting
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 -- Max Singapore --- |
#7
|
|||
|
|||
Conditional Formatting
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" wrote: 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 -- Max Singapore --- |
#8
|
|||
|
|||
Conditional Formatting
John,
Think its best that you start a new thread in this MS vba forum: http://social.msdn.microsoft.com/For...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 .. --- Max Singapore ----- |
#9
|
|||
|
|||
Conditional Formatting
Max
You have been a huge help. Thanks a lot. I will repost as you suggested. Thank You "Max" wrote: John, Think its best that you start a new thread in this MS vba forum: http://social.msdn.microsoft.com/For...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 .. --- Max Singapore ----- |
Thread Tools | |
Display Modes | |
|
|