find a value in a column, if it matched, highlight that column

[Don Doan]

Hi,
how can I write a macro to do look for a value in a column, say column "C",
if it's greater than or equal to 250,000 then highlight that row (in any
colour), if it's less than or equal to 250,000, then also hightlight that row
(in any color). It will keep on finding value until the end of the row (when
there is a blank line).

Thanks.

 

[Bob Phillips]

Try conditional formatting.

Select all target rows, starting in row 1
Menu Format>Conditional Formatting

Change Condition 1 to Formula is
Add a formulae of =$C1>25000
Select Patterns tab and choose a colour
OK
Click Add>>
Change Condition 1 to Formula is
Add a formulae of =$C1<=25000
Select Patterns tab and choose another colour
OK
OK



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Don Doan]

Hi,
I understand that conditional formatting can be used to do just that.
However, conditional formatting only allow different 3 conditions.
I guess the reason i'm asking for help with macro because through Excel
codes, I can add more conditions later on if I ever needed it.

 

[Bob Phillips]

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "C:C" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case > 3000: .Entirerow.Interior.ColorIndex = 3 'red
Case > 2500: .Entirerow.Interior.ColorIndex = 6 'yellow
Case >1500: .Entirerow.Interior.ColorIndex = 5 'blue
Case >1000: .Entirerow.Interior.ColorIndex = 10 'green
'etc.
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Don Doan]

Oh, thank you so much for the details..I will test it out.