Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel highlight row based on criteria

Posted on 2014-10-14
10
Medium Priority
?
198 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:J N
[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 49

Expert Comment

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

Author Comment

by:J N
ID: 40381279
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40381288
Is a non-formula solution OK?
0
Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

 
LVL 6

Author Comment

by:J N
ID: 40381294
Would it prompt the user or would it just work?
0
 
LVL 49

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:J N
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 49

Accepted Solution

by:
Martin Liss earned 2000 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:J N
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:J N
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 49

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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to edit text. This includes Font, Spacing, Resizing, Color, and other special text options.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

715 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