Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel highlight row based on criteria

Posted on 2014-10-14
10
Medium Priority
?
205 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
Gain an elementary understanding of Blockchain technology.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

926 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