Solved

Color Excel rows based on condition

Posted on 2011-02-10
23
354 Views
Last Modified: 2012-05-11
Hello i have a requirement

I need to write a macro to color the excel rows if Col L has the text "Done"
If Col L .value = "Done" then row = green else RED

the row range is A:AA

TIA
0
Comment
Question by:ExpertHelp79
23 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
Dear

you don't need a macro for this. You can use conditional formating.


kind regards

Eric
0
 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 250 total points
Comment Utility
Here is a example of how it is done.

You can copy and paste the format with the brush..


Kind regards

Eric
GreenDone.xls
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
You want to color the ENTIRE SHEET?

So sheet has either green or red rows in its entirety?

Dave
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
Dear,

You need to change one thing to be able to copy..
=$L$3="Done" in the original conditional formatting of row 3 you see this
=$L3="Done" that is what it should be.. now the row number changes with the paste of the format.


Kind regards

Eric
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I think conditional formatting on the ENTIRE column would be system intensive.

Here's code for the first 100 rows:

Sub ColorGreenOrRed()
Dim myCell As Range

    For Each myCell In Range("L1:L100") 'change to L:L for ENTIRE column
        If myCell.Value = "Done" Then
            myCell.EntireRow.Color = 5287936  'green
        Else
            myCell.EntireRow.Color = 255
        End If
    Next myCell

End Sub

Dave
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
Why delete question when the answers are given in both standard EXCEL and macro.
What does NS mean?

question is answered and should be rewarded

Kr

Eric
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
Oh yes I Object because of reason above..
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I'm confused too.  Anyway for the first 100 columns (modify below see note for entire column) this catches the columns A:AA...

Sub ColorGreenOrRed()
Dim myCell As Range

    For Each myCell In Range("L1:L100") 'change to L:L for ENTIRE column
        If myCell.Value = "Done" Then
            Range(Cells(myCell.Row, 1), Cells(myCell.Row, 27)).Interior.Color = 5287936   'green
        Else
            Range(Cells(myCell.Row, 1), Cells(myCell.Row, 27)).Interior.Color = 255
        End If
    Next myCell

End Sub

Dave
0
 
LVL 2

Author Comment

by:ExpertHelp79
Comment Utility
Sub Update_Row_Colors()

    Dim LRow As Integer
    Dim LCell As String
    Dim LColorCells As String
    'Start at row 7
    LRow = 3

    'Update row colors for the first 2000 rows
    While LRow < 1000
        LCell = "H" & LRow
        'Color will changed in columns A to K
        LColorCells = "A" & LRow & ":" & "AA" & LRow

        Select Case Range(LCell).Value

            'Set row color to light blue
            Case "Done"
                Range(LColorCells).Interior.ColorIndex = 35
                Range(LColorCells).Interior.Pattern = xlSolid

            'Set row color to light green
            'Case "030087"
             '   Rows(LRow & ":" & LRow).Select
              ''  Range(LColorCells).Interior.ColorIndex = 35
                'Range(LColorCells).Interior.Pattern = xlSolid

            'Set row color to light yellow
            'Case "063599"
             '   Rows(LRow & ":" & LRow).Select
              '  Range(LColorCells).Interior.ColorIndex = 19
               ' Range(LColorCells).Interior.Pattern = xlSolid

            'Default all other rows to no color
            Case Else
                Rows(LRow & ":" & LRow).Select
                Range(LColorCells).Interior.ColorIndex = 46

        End Select

        LRow = LRow + 1
    Wend

    Range("H1").Select

End Sub

Implemented
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
Well Expert help,

With the macro solution you will have to press run macro each time you want to update the row

With the conditional formatting you don't have to do anything. Colours will come and go automaticaly.

Please have a look at the attached excel. I formatted range a1 to K2000 to match your marco.

Kind regards

Eric
GreenDone.xls
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
My solution looks simpler than what you implemented.  appears you've already gone beyond the scope.

Glad you worked it out, anyway...

PS - even if you figure it out yourself, if there are EE's providing solutions, you can reward what you think is the best and move on.

Points are really just a reward for folks trying to help.  Its not like there's anything driving us to help other than we enjoy it.

Take care and happy computing!

Cheers,

Dave
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 250 total points
Comment Utility
@Eric - great tip.  I tried to do conitional (haven't done as much, but building competence) for entire rows looking at entire column and my system really dragged down.

Now, I"m trying to create it the way you did and get this... what gives,lol?

Dave
colorRow-conditional.xlsm
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
Hi Dave, you have to place a $ before the column L. if you do not, the columns L shift to M and so on ....

Try copying the format of row4 from my excel ans paste it in yours...



Kind regards


Eric
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Magic!

Tricky...

Thanks,

Dave
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
Hi Dave,

no sweat,
just hope the Experthelp can see this also..
He really doens't need the marco to do the job.
With conditional formatting it is much more neat..

only problem with excel version < 2007 is that the conditions is limited to 3
excel 2007 or 2010 can do mutiple conditions, just keep on adding....

Kr

Eric
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Hehe...

When I first did it with conditional formatting for this question, I used the formula =sumproduct(--(L:L="Done))>0
and it was a DRAIN.  only a few mins ago I reasoned that would flag all conditions RED if ANY element inn column L was "Done"....

Then, it hit me it would be a big drain on column L:L

I believe your solution is the best and much more efficient and dynamic (versus running macro) but could also be a drain on the ENTIRE worksheet as originally stated, lol.

:)

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
>>Then, it hit me it would be a big drain on column L:L

that is, if running as a macro...

Dave
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
Hi Dave,

Even if you would conditional format all rows until 65000
You would not see a drain.. no problem with computing time as only the row you change the L will update.

If you would change it so the whole range would change colour when changing 1 cell content that would cause a drain, but so would the macro solution, updating colours cost time in Excel...and if you want to update a lot...


Kind regards

Eric
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
Comment Utility
It seems that experthelp did solve his own question? ID:34868869
if the anwer that I did give nor dlmill's did help to achieve to this answer the question might be deleted, if not experthelp should give point to whom he thinks did help to solve this question.

kind regards


Eric
0
 
LVL 24

Expert Comment

by:broomee9
Comment Utility
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Outlook Free & Paid Tools
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now