Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Color Excel rows based on condition

Posted on 2011-02-10
23
Medium Priority
?
371 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
[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
23 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34868757
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 1000 total points
ID: 34868764
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 42

Expert Comment

by:dlmille
ID: 34868770
You want to color the ENTIRE SHEET?

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

Dave
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34868776
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 42

Expert Comment

by:dlmille
ID: 34868780
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
ID: 34868789
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
ID: 34868792
Oh yes I Object because of reason above..
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34868806
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
ID: 34868869
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
ID: 34869014
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
 
LVL 42

Expert Comment

by:dlmille
ID: 34869037
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 42

Assisted Solution

by:dlmille
dlmille earned 1000 total points
ID: 34869149
@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
ID: 34869440
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 42

Expert Comment

by:dlmille
ID: 34869535
Magic!

Tricky...

Thanks,

Dave
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34869674
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 42

Expert Comment

by:dlmille
ID: 34869682
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 42

Expert Comment

by:dlmille
ID: 34869687
>>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
ID: 34869719
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
ID: 35179414
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:Tracy
ID: 35356927
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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
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 demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

636 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