| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting the target cell
I'm trying to set in red the cell in the Colamn 9 if the value is 'No'.
There is some conditional formatting as well - if there is some value it's green, if it's empty it's without any collar. I'm using the following code to make it red if it's "No": If Target.Column = 9 Then With Target(1, 1) If Target.Value < "" Then If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .ClearContents End If End With End If But, it's getting red when I enter 'No' and after that clean the cell. But, I need it red when the cell value is "No". Could anybody advise anything. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formatting the target cell
Hi Alex,
Try replacing: If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If with: If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid Else .Interior.ColorIndex = xlNone End If --- Regards, Norman "Alex" wrote in message ... I'm trying to set in red the cell in the Colamn 9 if the value is 'No'. There is some conditional formatting as well - if there is some value it's green, if it's empty it's without any collar. I'm using the following code to make it red if it's "No": If Target.Column = 9 Then With Target(1, 1) If Target.Value < "" Then If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .ClearContents End If End With End If But, it's getting red when I enter 'No' and after that clean the cell. But, I need it red when the cell value is "No". Could anybody advise anything. Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formatting the target cell
Thank you very much, Norman.
I did. But, it's getting red not when I choose "No" but when I clean the cell. Any ideas why? Thanks "Norman Jones" wrote: Hi Alex, Try replacing: If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If with: If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid Else .Interior.ColorIndex = xlNone End If --- Regards, Norman "Alex" wrote in message ... I'm trying to set in red the cell in the Colamn 9 if the value is 'No'. There is some conditional formatting as well - if there is some value it's green, if it's empty it's without any collar. I'm using the following code to make it red if it's "No": If Target.Column = 9 Then With Target(1, 1) If Target.Value < "" Then If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .ClearContents End If End With End If But, it's getting red when I enter 'No' and after that clean the cell. But, I need it red when the cell value is "No". Could anybody advise anything. Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formatting the target cell
Hi Alex,
Try: '=================== Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 9 Then With Target(1) If .Value < "" Then If .Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .Interior.ColorIndex = xlNone End If End With End If '<<=================== --- Regards, Norman "Alex" wrote in message ... Thank you very much, Norman. I did. But, it's getting red not when I choose "No" but when I clean the cell. Any ideas why? Thanks "Norman Jones" wrote: Hi Alex, Try replacing: If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If with: If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid Else .Interior.ColorIndex = xlNone End If --- Regards, Norman "Alex" wrote in message ... I'm trying to set in red the cell in the Colamn 9 if the value is 'No'. There is some conditional formatting as well - if there is some value it's green, if it's empty it's without any collar. I'm using the following code to make it red if it's "No": If Target.Column = 9 Then With Target(1, 1) If Target.Value < "" Then If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .ClearContents End If End With End If But, it's getting red when I enter 'No' and after that clean the cell. But, I need it red when the cell value is "No". Could anybody advise anything. Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formatting the target cell
Thank you Norman again.
We're almost there. But, the strange thing is it changes the collar in red when I go to the next cell and return back to this cell. The same is for the cleaning. "Norman Jones" wrote: Hi Alex, Try: '=================== Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 9 Then With Target(1) If .Value < "" Then If .Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .Interior.ColorIndex = xlNone End If End With End If '<<=================== --- Regards, Norman "Alex" wrote in message ... Thank you very much, Norman. I did. But, it's getting red not when I choose "No" but when I clean the cell. Any ideas why? Thanks "Norman Jones" wrote: Hi Alex, Try replacing: If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If with: If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid Else .Interior.ColorIndex = xlNone End If --- Regards, Norman "Alex" wrote in message ... I'm trying to set in red the cell in the Colamn 9 if the value is 'No'. There is some conditional formatting as well - if there is some value it's green, if it's empty it's without any collar. I'm using the following code to make it red if it's "No": If Target.Column = 9 Then With Target(1, 1) If Target.Value < "" Then If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .ClearContents End If End With End If But, it's getting red when I enter 'No' and after that clean the cell. But, I need it red when the cell value is "No". Could anybody advise anything. Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formatting the target cell
Hi Alex,
Firstly, I inadvertently dropped the closing line. Please replace: End If '<<=================== with: End If End Sub '<<=================== We're almost there. But, the strange thing is it changes the collar in red when I go to the next cell and return back to this cell. The same is for the cleaning. Did you paste the suggested code into the worksheet's code module, or did you, perhaps, change the procedure to: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If so, replace the procedue header with: Private Sub Worksheet_Change(ByVal Target As Range) As written, the suggested code should only colour (or remove the colour) when the cell is edited / deleted. --- Regards, Norman "Alex" wrote in message ... Thank you Norman again. We're almost there. But, the strange thing is it changes the collar in red when I go to the next cell and return back to this cell. The same is for the cleaning. "Norman Jones" wrote: Hi Alex, Try: '=================== Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 9 Then With Target(1) If .Value < "" Then If .Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .Interior.ColorIndex = xlNone End If End With End If '<<=================== --- Regards, Norman "Alex" wrote in message ... Thank you very much, Norman. I did. But, it's getting red not when I choose "No" but when I clean the cell. Any ideas why? Thanks "Norman Jones" wrote: Hi Alex, Try replacing: If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If with: If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid Else .Interior.ColorIndex = xlNone End If --- Regards, Norman "Alex" wrote in message ... I'm trying to set in red the cell in the Colamn 9 if the value is 'No'. There is some conditional formatting as well - if there is some value it's green, if it's empty it's without any collar. I'm using the following code to make it red if it's "No": If Target.Column = 9 Then With Target(1, 1) If Target.Value < "" Then If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .ClearContents End If End With End If But, it's getting red when I enter 'No' and after that clean the cell. But, I need it red when the cell value is "No". Could anybody advise anything. Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formatting the target cell
Tnank you very much, Norman.
You're right, I didn't change the header of the procedure from SelectChange to Change. Now, it's working perfectly. "Norman Jones" wrote: Hi Alex, Firstly, I inadvertently dropped the closing line. Please replace: End If '<<=================== with: End If End Sub '<<=================== We're almost there. But, the strange thing is it changes the collar in red when I go to the next cell and return back to this cell. The same is for the cleaning. Did you paste the suggested code into the worksheet's code module, or did you, perhaps, change the procedure to: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If so, replace the procedue header with: Private Sub Worksheet_Change(ByVal Target As Range) As written, the suggested code should only colour (or remove the colour) when the cell is edited / deleted. --- Regards, Norman "Alex" wrote in message ... Thank you Norman again. We're almost there. But, the strange thing is it changes the collar in red when I go to the next cell and return back to this cell. The same is for the cleaning. "Norman Jones" wrote: Hi Alex, Try: '=================== Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 9 Then With Target(1) If .Value < "" Then If .Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .Interior.ColorIndex = xlNone End If End With End If '<<=================== --- Regards, Norman "Alex" wrote in message ... Thank you very much, Norman. I did. But, it's getting red not when I choose "No" but when I clean the cell. Any ideas why? Thanks "Norman Jones" wrote: Hi Alex, Try replacing: If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If with: If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid Else .Interior.ColorIndex = xlNone End If --- Regards, Norman "Alex" wrote in message ... I'm trying to set in red the cell in the Colamn 9 if the value is 'No'. There is some conditional formatting as well - if there is some value it's green, if it's empty it's without any collar. I'm using the following code to make it red if it's "No": If Target.Column = 9 Then With Target(1, 1) If Target.Value < "" Then If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .ClearContents End If End With End If But, it's getting red when I enter 'No' and after that clean the cell. But, I need it red when the cell value is "No". Could anybody advise anything. Thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formatting the target cell
Norman, I didn't check it yesterday for all options. Somehow it's getting red
with any value - not just with "No". I'm doing it in a test sheet without any additional formatting. In the actual workshhet I have Column 9 as a target one. There in addition I have a conditional formatting : CellValueIs not equel to ="" - green. If I'm using the code there as: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 9 Then With Target(1) If .Value < "" Then If .Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .Interior.ColorIndex = xlNone End If End With End If End Sub It didn't turn to red at all. If I entered "No" it's green and when I clean it it turns temporary to a red for a second and after that getting blank. Thanks "Norman Jones" wrote: Hi Alex, Firstly, I inadvertently dropped the closing line. Please replace: End If '<<=================== with: End If End Sub '<<=================== We're almost there. But, the strange thing is it changes the collar in red when I go to the next cell and return back to this cell. The same is for the cleaning. Did you paste the suggested code into the worksheet's code module, or did you, perhaps, change the procedure to: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If so, replace the procedue header with: Private Sub Worksheet_Change(ByVal Target As Range) As written, the suggested code should only colour (or remove the colour) when the cell is edited / deleted. --- Regards, Norman "Alex" wrote in message ... Thank you Norman again. We're almost there. But, the strange thing is it changes the collar in red when I go to the next cell and return back to this cell. The same is for the cleaning. "Norman Jones" wrote: Hi Alex, Try: '=================== Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 9 Then With Target(1) If .Value < "" Then If .Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .Interior.ColorIndex = xlNone End If End With End If '<<=================== --- Regards, Norman "Alex" wrote in message ... Thank you very much, Norman. I did. But, it's getting red not when I choose "No" but when I clean the cell. Any ideas why? Thanks "Norman Jones" wrote: Hi Alex, Try replacing: If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If with: If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid Else .Interior.ColorIndex = xlNone End If --- Regards, Norman "Alex" wrote in message ... I'm trying to set in red the cell in the Colamn 9 if the value is 'No'. There is some conditional formatting as well - if there is some value it's green, if it's empty it's without any collar. I'm using the following code to make it red if it's "No": If Target.Column = 9 Then With Target(1, 1) If Target.Value < "" Then If Target.Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .ClearContents End If End With End If But, it's getting red when I enter 'No' and after that clean the cell. But, I need it red when the cell value is "No". Could anybody advise anything. Thanks |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formatting the target cell
Hi Alex,
Firstly, replace your code with the following version: '============= Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim rcell As Range Set rng = Intersect(Target.Cells, Columns(9)) If Not rng Is Nothing Then For Each rcell In rng.Cells With rcell If LCase(.Value) = "no" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid Else .Interior.ColorIndex = xlNone End If End With Next rcell End If End Sub '<<============= It didn't turn to red at all. If I entered "No" it's green and when I clean it it turns temporary to a red for a second and after that getting blank. Do you have any other event macros or are these cells subject to conditional formatting? --- Regards, Norman "Alex" wrote in message ... Norman, I didn't check it yesterday for all options. Somehow it's getting red with any value - not just with "No". I'm doing it in a test sheet without any additional formatting. In the actual workshhet I have Column 9 as a target one. There in addition I have a conditional formatting : CellValueIs not equel to ="" - green. If I'm using the code there as: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 9 Then With Target(1) If .Value < "" Then If .Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .Interior.ColorIndex = xlNone End If End With End If End Sub It didn't turn to red at all. If I entered "No" it's green and when I clean it it turns temporary to a red for a second and after that getting blank. Thanks |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formatting the target cell
Thank you very much, Norman.
It's working great on a sheet without the conditional formatting. But, it's not working on my actual sheet with a conditional formatting (see below). "Norman Jones" wrote: Hi Alex, Firstly, replace your code with the following version: '============= Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim rcell As Range Set rng = Intersect(Target.Cells, Columns(9)) If Not rng Is Nothing Then For Each rcell In rng.Cells With rcell If LCase(.Value) = "no" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid Else .Interior.ColorIndex = xlNone End If End With Next rcell End If End Sub '<<============= It didn't turn to red at all. If I entered "No" it's green and when I clean it it turns temporary to a red for a second and after that getting blank. Do you have any other event macros or are these cells subject to conditional formatting? --- Regards, Norman "Alex" wrote in message ... Norman, I didn't check it yesterday for all options. Somehow it's getting red with any value - not just with "No". I'm doing it in a test sheet without any additional formatting. In the actual workshhet I have Column 9 as a target one. There in addition I have a conditional formatting : CellValueIs not equel to ="" - green. If I'm using the code there as: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 9 Then With Target(1) If .Value < "" Then If .Value = "No" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid End If Else .Interior.ColorIndex = xlNone End If End With End If End Sub It didn't turn to red at all. If I entered "No" it's green and when I clean it it turns temporary to a red for a second and after that getting blank. Thanks |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formatting the target cell
Hi Alex,
Conditional formatting takes precedence over standard formats. Is the conditional format condition constant for the column "I" cells of interest and, if so, what is/are the condition(s)? --- Regards, Norman "Alex" wrote in message ... Thank you very much, Norman. It's working great on a sheet without the conditional formatting. But, it's not working on my actual sheet with a conditional formatting (see below). "Norman Jones" wrote: Hi Alex, Firstly, replace your code with the following version: '============= Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim rcell As Range Set rng = Intersect(Target.Cells, Columns(9)) If Not rng Is Nothing Then For Each rcell In rng.Cells With rcell If LCase(.Value) = "no" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid Else .Interior.ColorIndex = xlNone End If End With Next rcell End If End Sub '<<============= It didn't turn to red at all. If I entered "No" it's green and when I clean it it turns temporary to a red for a second and after that getting blank. Do you have any other event macros or are these cells subject to conditional formatting? --- Regards, Norman |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formatting the target cell
I have a conditional formatting for 12 columns (including 9th) on the sheet :
CellValueIs not equel to ="" then green. It's working well. This 9th column with the Validation Yes, No, N/A. When the value is entered it's green, if there is no value it's blank. I was asked to make it red if it's No. Adding the additional conditional formatting as CellValueIs equel to ="No" then red is not working. Thanks "Norman Jones" wrote: Hi Alex, Conditional formatting takes precedence over standard formats. Is the conditional format condition constant for the column "I" cells of interest and, if so, what is/are the condition(s)? --- Regards, Norman "Alex" wrote in message ... Thank you very much, Norman. It's working great on a sheet without the conditional formatting. But, it's not working on my actual sheet with a conditional formatting (see below). "Norman Jones" wrote: Hi Alex, Firstly, replace your code with the following version: '============= Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim rcell As Range Set rng = Intersect(Target.Cells, Columns(9)) If Not rng Is Nothing Then For Each rcell In rng.Cells With rcell If LCase(.Value) = "no" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid Else .Interior.ColorIndex = xlNone End If End With Next rcell End If End Sub '<<============= It didn't turn to red at all. If I entered "No" it's green and when I clean it it turns temporary to a red for a second and after that getting blank. Do you have any other event macros or are these cells subject to conditional formatting? --- Regards, Norman |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
formatting the target cell
Hi Alex,
For the relevant column I cells, try using conditional formats and scrap the code, e.g: Formula Is =AND($I1<"",$I1<"No") Green Cell Value Is equal to ="No" Red --- Regards, Norman "Alex" wrote in message ... I have a conditional formatting for 12 columns (including 9th) on the sheet : CellValueIs not equel to ="" then green. It's working well. This 9th column with the Validation Yes, No, N/A. When the value is entered it's green, if there is no value it's blank. I was asked to make it red if it's No. Adding the additional conditional formatting as CellValueIs equel to ="No" then red is not working. Thanks "Norman Jones" wrote: Hi Alex, Conditional formatting takes precedence over standard formats. Is the conditional format condition constant for the column "I" cells of interest and, if so, what is/are the condition(s)? --- Regards, Norman "Alex" wrote in message ... Thank you very much, Norman. It's working great on a sheet without the conditional formatting. But, it's not working on my actual sheet with a conditional formatting (see below). "Norman Jones" wrote: Hi Alex, Firstly, replace your code with the following version: '============= Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim rcell As Range Set rng = Intersect(Target.Cells, Columns(9)) If Not rng Is Nothing Then For Each rcell In rng.Cells With rcell If LCase(.Value) = "no" Then .Interior.ColorIndex = 3 .Interior.Pattern = xlSolid Else .Interior.ColorIndex = xlNone End If End With Next rcell End If End Sub '<<============= It didn't turn to red at all. If I entered "No" it's green and when I clean it it turns temporary to a red for a second and after that getting blank. Do you have any other event macros or are these cells subject to conditional formatting? --- Regards, Norman |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formatting % and KPI target in excel 2003 | Excel Discussion (Misc queries) | |||
| Target cell reference moves when target is cut and pasted | Excel Discussion (Misc queries) | |||
| Maintain cell formatting in target cell when using Paste | Excel Discussion (Misc queries) | |||
| Ranges:Target in Worksheet_SelectionChange(ByVal Target As Range) | Excel Programming | |||
| How find if target is object in Worksheet_Change (ByVal Target As.. ?) | Excel Programming | |||
Linear Mode
