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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Don Doan - 28 Jan 2008 17:28 GMT

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.

Reply to this Message

Bob Phillips - 28 Jan 2008 17:41 GMT

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

Signature

---
HTH

Bob

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

> Hi,
> how can I write a macro to do look for a value in a column, say column
[quoted text clipped - 7 lines]
>
> Thanks.

Reply to this Message

Don Doan - 28 Jan 2008 18:15 GMT

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.

> Try conditional formatting.
>
[quoted text clipped - 23 lines]
> >
> > Thanks.

Reply to this Message

Bob Phillips - 28 Jan 2008 22:38 GMT

'-----------------------------------------------------------------
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.

Signature

---
HTH

Bob

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

> Hi,
> I understand that conditional formatting can be used to do just that.
[quoted text clipped - 31 lines]
>> >
>> > Thanks.

Reply to this Message

Don Doan - 29 Jan 2008 14:23 GMT

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??

> '-----------------------------------------------------------------
> Private Sub Worksheet_Change(ByVal Target As Range)
[quoted text clipped - 59 lines]
> >> >
> >> > Thanks.

Reply to this Message