Solved

Excel highlight row based on criteria

Posted on 2014-10-14
10
181 Views
Last Modified: 2014-10-15
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
0
Comment
Question by:M. Jayme Nagy
  • 5
  • 5
10 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40381259
Can you attach your workbook please?
0
 
LVL 6

Author Comment

by:M. Jayme Nagy
ID: 40381279
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40381288
Is a non-formula solution OK?
0
Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

 
LVL 6

Author Comment

by:M. Jayme Nagy
ID: 40381294
Would it prompt the user or would it just work?
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40381308
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
 
LVL 6

Author Comment

by:M. Jayme Nagy
ID: 40381348
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
 
LVL 46

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40381422
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
 
LVL 6

Author Comment

by:M. Jayme Nagy
ID: 40382043
Thanks that works exactly how i would like it !

--Cell problem was just the local machine being dumb

thanks
0
 
LVL 6

Author Comment

by:M. Jayme Nagy
ID: 40382054
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40382250
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

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

With the internet and the ease of information transference, many professional jobs can be done anywhere today.  Why should it make a difference whether an x-ray is read in India or the United States as long as the radiologist is qualified?   Outso…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question