Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formatting the target cell
I got a help from Norman yesterday regading this question. It was almost
resolved, thans a lot. But, 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 |
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) | |||
formatting the target cell | Excel Programming | |||
How find if target is object in Worksheet_Change (ByVal Target As.. ?) | Excel Programming |