Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More than 3 cell highlite colors ?
I have values created by a formula from info pasted into other cells
in X17 to X24, Y17 to Y24. Again in AC17 to AC24, AD17 to AD24. These IF formulas use ="","", to present a blank cell if no data is available. In G29 to G36 I use =X17 to X24 and I29 is =Y17 etc,to move this result to a report cover page. I use CF to change the cell background color in G29 to G36 and I29 to I36 depending on what values appear there, as G29 = 95%, green I29 = A, green, etc. It works great, but.... Now I need a fourth color. Add in's are forbidden. I have VB that will change cell color in G29 to G36, etc, but only when values are typed directly in these cells. It will not update G29 when the value in X17 changes. Is there a way to modify it so that it will copy the value in X17 to G29, etc, and highlite the cell to a specified color per value ? Using Office 2000. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
More than 3 cell highlite colors ?
Hopefully, this will help:
http://www.mcgimpsey.com/excel/conditional6.html "rmayer" wrote: I have values created by a formula from info pasted into other cells in X17 to X24, Y17 to Y24. Again in AC17 to AC24, AD17 to AD24. These IF formulas use ="","", to present a blank cell if no data is available. In G29 to G36 I use =X17 to X24 and I29 is =Y17 etc,to move this result to a report cover page. I use CF to change the cell background color in G29 to G36 and I29 to I36 depending on what values appear there, as G29 = 95%, green I29 = A, green, etc. It works great, but.... Now I need a fourth color. Add in's are forbidden. I have VB that will change cell color in G29 to G36, etc, but only when values are typed directly in these cells. It will not update G29 when the value in X17 changes. Is there a way to modify it so that it will copy the value in X17 to G29, etc, and highlite the cell to a specified color per value ? Using Office 2000. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
More than 3 cell highlite colors ?
Thanks, I checked the page you refer to. It deals with changing font
color. I need to change cell color ? Wouldn't that be a different method ? "JMB" wrote: Hopefully, this will help: http://www.mcgimpsey.com/excel/conditional6.html "rmayer" wrote: I have values created by a formula from info pasted into other cells in X17 to X24, Y17 to Y24. Again in AC17 to AC24, AD17 to AD24. These IF formulas use ="","", to present a blank cell if no data is available. In G29 to G36 I use =X17 to X24 and I29 is =Y17 etc,to move this result to a report cover page. I use CF to change the cell background color in G29 to G36 and I29 to I36 depending on what values appear there, as G29 = 95%, green I29 = A, green, etc. It works great, but.... Now I need a fourth color. Add in's are forbidden. I have VB that will change cell color in G29 to G36, etc, but only when values are typed directly in these cells. It will not update G29 when the value in X17 changes. Is there a way to modify it so that it will copy the value in X17 to G29, etc, and highlite the cell to a specified color per value ? Using Office 2000. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
More than 3 cell highlite colors ?
Sorry, my mistake.
I assume your reference to VB means you are using an event handler - perhaps the worksheet Change or Worksheet Calculate event? I would think either one of these would work to test the values in column G and I and change the cell color. My thought would be something like this. Right click on you sheet tab, select view code and paste it into your code module: Private Sub Worksheet_Change(ByVal Target As Range) If Range("G29").Value = 0.95 Then _ Range("G29").Interior.ColorIndex = 10 If Range("I29").Value = "A" Then _ Range("I29").Interior.ColorIndex = 10 End Sub The event handler will fire if cell G29 or X17 changes (or any cell on the worksheet - for that matter). If that doesn't help, please post your code. "rmayer" wrote: Thanks, I checked the page you refer to. It deals with changing font color. I need to change cell color ? Wouldn't that be a different method ? "JMB" wrote: Hopefully, this will help: http://www.mcgimpsey.com/excel/conditional6.html "rmayer" wrote: I have values created by a formula from info pasted into other cells in X17 to X24, Y17 to Y24. Again in AC17 to AC24, AD17 to AD24. These IF formulas use ="","", to present a blank cell if no data is available. In G29 to G36 I use =X17 to X24 and I29 is =Y17 etc,to move this result to a report cover page. I use CF to change the cell background color in G29 to G36 and I29 to I36 depending on what values appear there, as G29 = 95%, green I29 = A, green, etc. It works great, but.... Now I need a fourth color. Add in's are forbidden. I have VB that will change cell color in G29 to G36, etc, but only when values are typed directly in these cells. It will not update G29 when the value in X17 changes. Is there a way to modify it so that it will copy the value in X17 to G29, etc, and highlite the cell to a specified color per value ? Using Office 2000. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
More than 3 cell highlite colors ?
Forgot to remove the color if the condition is not met.
Private Sub Worksheet_Change(ByVal Target As Range) With Range("G29") If .Value = 0.95 Then .Interior.ColorIndex = 10 Else: .Interior.ColorIndex = xlNone End If End With With Range("I29") If UCase(.Value) = "A" Then .Interior.ColorIndex = 10 Else: .Interior.ColorIndex = xlNone End If End With End Sub "rmayer" wrote: Thanks, I checked the page you refer to. It deals with changing font color. I need to change cell color ? Wouldn't that be a different method ? "JMB" wrote: Hopefully, this will help: http://www.mcgimpsey.com/excel/conditional6.html "rmayer" wrote: I have values created by a formula from info pasted into other cells in X17 to X24, Y17 to Y24. Again in AC17 to AC24, AD17 to AD24. These IF formulas use ="","", to present a blank cell if no data is available. In G29 to G36 I use =X17 to X24 and I29 is =Y17 etc,to move this result to a report cover page. I use CF to change the cell background color in G29 to G36 and I29 to I36 depending on what values appear there, as G29 = 95%, green I29 = A, green, etc. It works great, but.... Now I need a fourth color. Add in's are forbidden. I have VB that will change cell color in G29 to G36, etc, but only when values are typed directly in these cells. It will not update G29 when the value in X17 changes. Is there a way to modify it so that it will copy the value in X17 to G29, etc, and highlite the cell to a specified color per value ? Using Office 2000. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
More than 3 cell highlite colors ?
Thanks, this looks good. It will be a few hours before I'll get
a chance to try it. I'll post the results. "JMB" wrote: Forgot to remove the color if the condition is not met. Private Sub Worksheet_Change(ByVal Target As Range) With Range("G29") If .Value = 0.95 Then .Interior.ColorIndex = 10 Else: .Interior.ColorIndex = xlNone End If End With With Range("I29") If UCase(.Value) = "A" Then .Interior.ColorIndex = 10 Else: .Interior.ColorIndex = xlNone End If End With End Sub "rmayer" wrote: Thanks, I checked the page you refer to. It deals with changing font color. I need to change cell color ? Wouldn't that be a different method ? "JMB" wrote: Hopefully, this will help: http://www.mcgimpsey.com/excel/conditional6.html "rmayer" wrote: I have values created by a formula from info pasted into other cells in X17 to X24, Y17 to Y24. Again in AC17 to AC24, AD17 to AD24. These IF formulas use ="","", to present a blank cell if no data is available. In G29 to G36 I use =X17 to X24 and I29 is =Y17 etc,to move this result to a report cover page. I use CF to change the cell background color in G29 to G36 and I29 to I36 depending on what values appear there, as G29 = 95%, green I29 = A, green, etc. It works great, but.... Now I need a fourth color. Add in's are forbidden. I have VB that will change cell color in G29 to G36, etc, but only when values are typed directly in these cells. It will not update G29 when the value in X17 changes. Is there a way to modify it so that it will copy the value in X17 to G29, etc, and highlite the cell to a specified color per value ? Using Office 2000. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
More than 3 cell highlite colors ?
'----------------------------------------------------------------- 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 (replace somewhere in email address with gmail if mailing direct) "rmayer" wrote in message ... I have values created by a formula from info pasted into other cells in X17 to X24, Y17 to Y24. Again in AC17 to AC24, AD17 to AD24. These IF formulas use ="","", to present a blank cell if no data is available. In G29 to G36 I use =X17 to X24 and I29 is =Y17 etc,to move this result to a report cover page. I use CF to change the cell background color in G29 to G36 and I29 to I36 depending on what values appear there, as G29 = 95%, green I29 = A, green, etc. It works great, but.... Now I need a fourth color. Add in's are forbidden. I have VB that will change cell color in G29 to G36, etc, but only when values are typed directly in these cells. It will not update G29 when the value in X17 changes. Is there a way to modify it so that it will copy the value in X17 to G29, etc, and highlite the cell to a specified color per value ? Using Office 2000. Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
More than 3 cell highlite colors ?
Thanks for the help, but I don't know enough about the code to be able to
modify it to cover the all the variables. It doesn't update the color when new data is pasted into the worksheet (=x17) etc. The code necessary to cover this is probably to complex to be pratical anyway. Thanks again. "Bob Phillips" wrote: '----------------------------------------------------------------- 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 (replace somewhere in email address with gmail if mailing direct) "rmayer" wrote in message ... I have values created by a formula from info pasted into other cells in X17 to X24, Y17 to Y24. Again in AC17 to AC24, AD17 to AD24. These IF formulas use ="","", to present a blank cell if no data is available. In G29 to G36 I use =X17 to X24 and I29 is =Y17 etc,to move this result to a report cover page. I use CF to change the cell background color in G29 to G36 and I29 to I36 depending on what values appear there, as G29 = 95%, green I29 = A, green, etc. It works great, but.... Now I need a fourth color. Add in's are forbidden. I have VB that will change cell color in G29 to G36, etc, but only when values are typed directly in these cells. It will not update G29 when the value in X17 changes. Is there a way to modify it so that it will copy the value in X17 to G29, etc, and highlite the cell to a specified color per value ? Using Office 2000. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
worksheet set to highlite a cell while data is being entered | Excel Discussion (Misc queries) | |||
why does more than one cell highlite when one cell is selected | Excel Discussion (Misc queries) | |||
Highlite the cell if above 5:31 | Excel Worksheet Functions | |||
Can't format font colors or cell fill-in colors | Excel Discussion (Misc queries) | |||
how do I highlite text within a cell (specific characters) | Excel Discussion (Misc queries) |