VBA that mimics Conditional Formating

  • Thread starter Thread starter Roger R
  • Start date Start date
R

Roger R

I am trying to find a way to change to color of a cell (s)
in conjunction with the value of that cell, then remove
the value and have to color remain intact. Conditional
formating works great up to the point of removing the
value.
Please help.
 
Hi Roger,

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
Target.Interior.ColorIndex = 3 'red
End If

ws_exit:
Application.EnableEvents = True
End Sub

On the worksheet that you want this event to happen, right-click the sheet
tab, select View Code, and then just paste it in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,
Thanks for the help, but I need a little more info.
How do I dictate the value of the cell that I want to
color block.
(eg- I want any cell with the value of >= 10 to be yellow.)
Thanks again
 
Roger,

The range to be tested for is A1:A100 in my example.

To cater for values something like

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
Select Case .Value
Case Is >= 10: .Interior.ColorIndex = 6 'yellow
Case Is >= 5: .Interior.ColorIndex = 3 'red
Case Else: .Interior.ColorIndex = 5 'blue
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

and adapt to suit.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Excellent thank you very much
-----Original Message-----
Roger,

The range to be tested for is A1:A100 in my example.

To cater for values something like

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A100")) Is Nothing Then
With Target
Select Case .Value
Case Is >= 10: .Interior.ColorIndex = 6 'yellow
Case Is >= 5: .Interior.ColorIndex = 3 'red
Case Else: .Interior.ColorIndex = 5 'blue
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

and adapt to suit.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)




.
Click to expand...
 
Back
Top