• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

A Little Complicated 4 Conditional Formatting

A

a4vtt

New Member
Hello All,


I need help with a 4 conditional formatting in Excel 2003 (NOT 2007), please. I googled many websites, and so far nothing has the answer for me.


I'd like the font of the row between column A to column D only to change color to:

- Blue when column C of that row displays "Morning"

- Orange when column C of that row displays "Afternoon"

- Green when column C of that row displays "Evening"

- Brown when column C of that row displays "Night" and in this case, that row also highlights in Pink between column A and column D only.


Please help me if you guys have knowledge. I was told that I need to use VBA, which I'm not very good at. And so far noone was able to help.


Thank you so much in advanced.
 
a4vtt

You have 2 options here


Use 3 Conditional Formats or VBA


3 Conditional Formats


You can use 3 Conditional formats for say Morning, Afternoon and Evening and then use the Default as Night. This wont work if you have other values like Dawn as a field value


VBA

try the following code (XL 2003 or less only)


Sub Four_Cond_Macro()


For Each c In Range("c1:c200") 'Change 200 to suit


If c.Value = "" Then Exit Sub


If UCase(c.Value) = "MORNING" Then

Range(Cells(c.Row, 1), Cells(c.Row, 4)).Select

Selection.Font.ColorIndex = 5


ElseIf UCase(c.Value) = "AFTERNOON" Then

Range(Cells(c.Row, 1), Cells(c.Row, 4)).Select

Selection.Font.ColorIndex = 46


ElseIf UCase(c.Value) = "EVENING" Then

Range(Cells(c.Row, 1), Cells(c.Row, 4)).Select

Selection.Font.ColorIndex = 4


ElseIf UCase(c.Value) = "NIGHT" Then

Range(Cells(c.Row, 1), Cells(c.Row, 4)).Select

Selection.Font.ColorIndex = 53

Selection.Interior.ColorIndex = 7

Selection.Interior.Pattern = xlSolid


Else

Range(Cells(c.Row, 1), Cells(c.Row, 4)).Select

Selection.Font.ColorIndex = 1

Selection.Interior.ColorIndex = xlNone

End If


Next


End Sub
 
Hello Hui,


I realized that you helped many users in this forum. Thank you so much for your help.


Back to my question, I'll go with the VBA option. Please help me a little bit more. The code you've written for me:


- doesn't run automatically as I enter the data. Another word, the font in the row only changes the color when I go to "View code" and run it mannually.

- After it runs, it stays at the row 200, could you please help me with an extra code, that would bring it back to where the data is (because sometimes we do not have the data in all 200 rows)

- Please show me how to change the highlight color in your code. For example, if I want to highlight the row in light green instead of pink.


Again, thank you so very much for your help, Hui!
 
a4vtt

The code below will run automatically and update the colors when you enter one of the keywords in Column C and only updates the current cell, doesn't go to C200


You need to shift the code onto the Code page which is associated with the Worksheet you are on, ie: Alt F11 and click on the page your data is on and paste it into the code window


To change colors have a look at the lines


Selection.Font.ColorIndex = 53 ' Font Color

Selection.Interior.ColorIndex = 7 ' background Color


To find colors start recording a macro

and click on the Font Color Button or Background Color and select the color you want and

close the macro

Check the code in the Macro editor to see what Color No. it is.


==========

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Column = 3 Then


If Target.Value = "" Then Exit Sub


If UCase(Target.Value) = "MORNING" Then

Range(Cells(Target.Row, 1), Cells(Target.Row, 4)).Select

Selection.Font.ColorIndex = 5


ElseIf UCase(Target.Value) = "AFTERNOON" Then

Range(Cells(Target.Row, 1), Cells(Target.Row, 4)).Select

Selection.Font.ColorIndex = 46


ElseIf UCase(Target.Value) = "EVENING" Then

Range(Cells(Target.Row, 1), Cells(Target.Row, 4)).Select

Selection.Font.ColorIndex = 4


ElseIf UCase(Target.Value) = "NIGHT" Then

Range(Cells(Target.Row, 1), Cells(Target.Row, 4)).Select

Selection.Font.ColorIndex = 53

Selection.Interior.ColorIndex = 7

Selection.Interior.Pattern = xlSolid


Else

Range(Cells(Target.Row, 1), Cells(Target.Row, 4)).Select

Selection.Font.ColorIndex = 1

Selection.Interior.ColorIndex = xlNone

End If


End If


End Sub
 
Hello Hui,


I need your help again, please.


Do you know why when I changed the conditions from "morning", "afternoon"... to "breakfast","lunch","dinner"... the code doesn't work anymore?


For future reference, please kindly show me how to make the code work when I need to change to something esle.


Thank you Hui again.
 
a4vtt

the lines which are like

If UCase(Target.Value) = "MORNING" Then

Look at the value in the Column C you have just typed

It converts it to Upper case and then Checks it against the word at the end in this case MORNING

I do this so it doesn't matter what the user types ie: Morning, MORNING and MoRnInG are all the same after being converted to Upper Case


The words at the end must be in Upper Case ie : BREAKFAST not breakfast


Hope that helps
 
Hello Hui,


The program worked. However, when I started applying the protection condition "Allow Users to Edit Ranges" feature in Excel, the code you had written for me has debugging error message.


Could you please help me how we can go about this?


Thanks so much, Hui.
 
"Allow Users to Edit Ranges" and then "Protect Sheet" then the code has debugging error message.
 
a4vtt


You may need to add the following lines into the macro


after the first line add


ActiveSheet.Unprotect


and before the last line 'End Sub' add


ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
 
You must log in or register to reply here.
Back
Top