Conditional formating (VBA)

[Dejan]

Hi

I use folowing private macroes, that in ColB changes value "Da" to "Ne" and "Ne" to "Da" with RightClick.

Private Sub Worksheet_BeforeRightClick(ByVal Target _
As Excel.Range, Cancel As Boolean)
If Target.Column > 3 Then
Exit Sub
End If
If Target.Row< 2 Then Exit Sub
On Error Resume Next
Cancel = True
If ActiveCell.Column = 3 And ActiveCell.Row > 2 Then
End If
If Target = "Da" Then
Target = "Ne"
GoTo E:
End If
If Target = "Ne" Then Target = "Da"
If Target = "Da" Then Target.Offset(0, 1) = Date
E:

End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveSheet.Unprotect ("irenica")
If Target.Cells.Count = 1 And Target.Column = 3 Then
Target.Offset(0, 1) = ""
End If

On Error Resume Next
If Target.Column<> 3 Then Exit Sub
If Target.Value<> "void" Then Exit Sub
Target.Select
Target = "Void"

Application.EnableEvents = False
Target.Resize(, Columns.Count - Target.Column).Offset(, 0).ClearContents
Application.EnableEvents = True

'ActiveSheet.Protect , DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub


Now I would like to do:

If in ColB, row that is selected with rightclick is "Da", fill with green colour all cells that are<> ""

in ColB, row that is selected with rightclick is "Ne", fill with red colour all cells that are<> ""


If somebody can help me, I would apreceate it.

THNX
This message was edited by Dejan on 2002-11-05 13:33

 

[Dejan]

Please??? Anybody

 

[NateO]

Howdy Dejan, I'll give this a crack, please try the following:<pre>
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
If Intersect(Target, [b2:b65536]) Is Nothing Then Exit Sub
If Target = "Da" Then
Cancel = True
Target = "Ne"
Rows(Target.Row & ":" & Target.Row).FormatConditions.Delete
Rows(Target.Row & ":" & Target.Row).FormatConditions.Add _
Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="="""""
Rows(Target.Row & ":" & _
Target.Row).FormatConditions(1).Interior.ColorIndex = 3
ElseIf Target = "Ne" Then
Cancel = True
Target = "Da"
Target(, 2) = Date
Rows(Target.Row & ":" & Target.Row).FormatConditions.Delete
Rows(Target.Row & ":" & Target.Row).FormatConditions.Add _
Type:=xlCellValue, Operator:=xlNotEqual, Formula1:="="""""
Rows(Target.Row & ":" & _
Target.Row).FormatConditions(1).Interior.ColorIndex = 4
End If
End Sub</pre>

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-11-05 20:37