Conditional formating (VBA)

Dejan

New Member
Joined
Oct 18, 2002
Messages
24
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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
 

Forum statistics

Threads
1,144,312
Messages
5,723,653
Members
422,508
Latest member
Lordkit1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top