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

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.

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

Try conditional formatting.

Select all target rows, starting in row 1
Menu FormatConditional Formatting

Change Condition 1 to Formula is
Add a formulae of =$C125000
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" wrote in message
...
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.

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

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" wrote:

Try conditional formatting.

Select all target rows, starting in row 1
Menu FormatConditional Formatting

Change Condition 1 to Formula is
Add a formulae of =$C125000
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" wrote in message
...
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.

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

'-----------------------------------------------------------------
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" wrote in message
...
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" wrote:

Try conditional formatting.

Select all target rows, starting in row 1
Menu FormatConditional Formatting

Change Condition 1 to Formula is
Add a formulae of =$C125000
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" wrote in message
...
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.

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

Oh, thank you so much for the details..I will test it out.
On the side, I really like to start learning how to write program in Excel,
where would be a good start??

"Bob Phillips" wrote:


'-----------------------------------------------------------------
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" wrote in message
...
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" wrote:

Try conditional formatting.

Select all target rows, starting in row 1
Menu FormatConditional Formatting

Change Condition 1 to Formula is
Add a formulae of =$C125000
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" wrote in message
...
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.