Solved

Excel highlight row based on criteria

Posted on 2014-10-14
10
182 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 47

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 47

Expert Comment

by:Martin Liss
ID: 40381288
Is a non-formula solution OK?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

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

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 47

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 47

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Photo Albums in PowerPoint Photo Albums are a very useful tool in PowerPoint and allow you quickly add a large number of images. The images can be formatted in a variety of ways so that you are able to create a professional looking presentation v…
I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

738 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