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
LVL 2
ExpertHelp79Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Eric ZwiekhorstConnect With a Mentor SAP Business ConsultantCommented:
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
 
Eric ZwiekhorstSAP Business ConsultantCommented:
Dear

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


kind regards

Eric
0
 
dlmilleCommented:
You want to color the ENTIRE SHEET?

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

Dave
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Eric ZwiekhorstSAP Business ConsultantCommented:
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
 
dlmilleCommented:
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
 
Eric ZwiekhorstSAP Business ConsultantCommented:
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
 
Eric ZwiekhorstSAP Business ConsultantCommented:
Oh yes I Object because of reason above..
0
 
dlmilleCommented:
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
 
ExpertHelp79Author Commented:
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
 
Eric ZwiekhorstSAP Business ConsultantCommented:
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
 
dlmilleCommented:
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
 
dlmilleConnect With a Mentor Commented:
@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
 
Eric ZwiekhorstSAP Business ConsultantCommented:
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
 
dlmilleCommented:
Magic!

Tricky...

Thanks,

Dave
0
 
Eric ZwiekhorstSAP Business ConsultantCommented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
>>Then, it hit me it would be a big drain on column L:L

that is, if running as a macro...

Dave
0
 
Eric ZwiekhorstSAP Business ConsultantCommented:
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
 
Eric ZwiekhorstSAP Business ConsultantCommented:
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
 
TracyVBA DeveloperCommented:
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
All Courses

From novice to tech pro — start learning today.