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