Excel highlight row based on criteria

Hi,

I am wondering if it is possible to highlight /bold a row based on a users input and if so how could i accomplish this.

Essentially, i have a spreadsheet where i enter the year end date (ex; March 31, 2014) in a cell. I then have rows that have are arranged in ascending order based on year (ex row 10 = 2012 row 11= 2013 row 12 = 2014). If the year end date cell is March 31, 2014 i want the 2014 row to be highlighted / bold.

thanks in advanced

Jayme
LVL 6
J NUnicorn wranglerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Can you attach your workbook please?
0
J NUnicorn wranglerAuthor Commented:
0
Martin LissOlder than dirtCommented:
Is a non-formula solution OK?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

J NUnicorn wranglerAuthor Commented:
Would it prompt the user or would it just work?
0
Martin LissOlder than dirtCommented:
Add this to Sheet1's code and it will operate automatically. Note that bold didn't show up very well.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

If Not Intersect(Target, Range("B5")) Is Nothing Then
    With Range("A10:A31")
        .Interior.ColorIndex = -4142
        Set r = .Find(What:=Year(Range("B5")), _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlNext)
    End With
    If Not r Is Nothing Then
        Cells(r.Row, r.Column).Interior.ColorIndex = 20
    End If
End If

End Sub

Open in new window

0
J NUnicorn wranglerAuthor Commented:
Hi,

i copied the code and it works pretty good. i am curious if it is possible to hghlight the entire row (within the table).

as well, B5 when i enter the date i now need to enter is as a day (ex 40000 = 7/6/2009). i want to keep the default date in excel as i have no clue what the day is i would enter it as 7/6/2009
0
Martin LissOlder than dirtCommented:
Here is modified code to highlight the table row.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range

If Not Intersect(Target, Range("B5")) Is Nothing Then
    With Range("A10:G31")
        .Interior.ColorIndex = -4142
        Set r = .Find(What:=Year(Range("B5")), _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlNext)
    End With
    If Not r Is Nothing Then
        Range("A" & r.Row & ":G" & r.Row).Interior.ColorIndex = 20
    End If
End If

End Sub

Open in new window


as well, B5 when i enter the date i now need to enter is as a day (ex 40000 = 7/6/2009). i want to keep the default date in excel as i have no clue what the day is i would enter it as 7/6/2009
I'm sorry but I don't know what you mean.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
J NUnicorn wranglerAuthor Commented:
Thanks that works exactly how i would like it !

--Cell problem was just the local machine being dumb

thanks
0
J NUnicorn wranglerAuthor Commented:
Hi,

Im a little curious about how to add columns as it looks as though the code would be set to end at column G

i can start a new question for this as well
0
Martin LissOlder than dirtCommented:
To add or subtract columns from the highlighting, just change the "G" in lines 5 (line 6 uses that range to change all rows in the range to their default color) and 14.

In any case you're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Office Productivity

From novice to tech pro — start learning today.