Link to home
Start Free TrialLog in
Avatar of ExpertHelp79
ExpertHelp79

asked on

Color Excel rows based on condition

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
Avatar of Eric Zwiekhorst
Eric Zwiekhorst
Flag of Netherlands image

Dear

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


kind regards

Eric
ASKER CERTIFIED SOLUTION
Avatar of Eric Zwiekhorst
Eric Zwiekhorst
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You want to color the ENTIRE SHEET?

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

Dave
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
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
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
Oh yes I Object because of reason above..
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
Avatar of ExpertHelp79
ExpertHelp79

ASKER

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
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
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Magic!

Tricky...

Thanks,

Dave
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
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
>>Then, it hit me it would be a big drain on column L:L

that is, if running as a macro...

Dave
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
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
Avatar of Tracy
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.