Colour Formatting Rows by Validation List

[Tomred]

My Objective: Simplified
Assuming a range A1:G15
A Validation list in column H

The Validation List options are: Blue,Pink,Green,Black,Grey,Yellow,Orange and None or '

Can anyone advise me how I create a Case Select code to change, the row range dependant on the option selected in the validation list.....or most efficient code to achieve my objective.

Or the most efficient code method to achieve the outcome I am seeking.

What I can't figure out 1:
How to incorporate the validation list selection to recognise the colour option selected.

What I can't figure out 2:
I want to be able to format a row any of colours in the list, so if it is black, would I need to clear the black format before changing it to say Orange, or is it possible to go straight from black to Orange?

What I have tried:
I am aware of the limitation of three formats using "Conditional Formatting" so want to expand the colour formatting possbilities using code, by selecting choice of format colour from a validation list.

I did a macro recording, selecting the appropriate option within the Validation List, and then formatting a row range....I experimented with the resulting code to no success, although I have a copy of VBA and Macros for MS Excel....theres a lot to think about....and I can't quite grasp what's required.

Code I have used in the passed for a similar but different worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_Handler

If Not Intersect(Target, Range("K1:K20")) Is Nothing Then
Application.EnableEvents = False
i = Target.Row
Select Case Target.Value
Case "Yes"
Range("A" & i & ":M" & i).Interior.ColorIndex = 6
Case "No"
Range("A" & i & ":M" & i).Interior.ColorIndex = 12
Case "W"
Range("A" & i & ":M" & i).Interior.ColorIndex = 18
Case "X"
Range("A" & i & ":M" & i).Interior.ColorIndex = 22
Case "Y"
Range("A" & i & ":M" & i).Interior.ColorIndex = 26
Case "Z"
Range("A" & i & ":M" & i).Interior.ColorIndex = 30
Case Else
Range("A" & i & ":M" & i).Interior.ColorIndex = xlNone
End Select
End If

Err_Handler:
Application.EnableEvents = True
End Sub


Any help, would be much appreciated.

Thanks in advance

 

[mikerickson]

Perhaps something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count = 1 And Target.Column = 8 And Target.Row < 16 Then

    With Target.EntireRow.Range("a1:g1")

        Select Case LCase(CStr(Target.Value))
            Case Is = "black"
                .Interior.ColorIndex = 1
            Case Is = "red"
                .Interior.ColorIndex = 3
            Case Is = "blue"
                .Interior.ColorIndex = 5
            Case Is = "pink"
                .Interior.ColorIndex = 7
            Case Else
                .Interior.ColorIndex = xlNone
        End Select

    End With
End If
End Sub

 

[Tomred]

Thanks for the reply Mike,

I'll give your code a try.....it certainly looks more abbreviated thatn my attempts!!

 

[Tomred]

Hi Mike,

Please advise a little further.........

I have opened a blank worksheet, inserted a module and entered the code. when I select a colour option from the validation list in column H, their is no colour change.

I have tried putting the code into This Worksheet, but that didn't resolve the issue either.

Can you comment the code for me, as I don't understand how the validation list is accounted for in it.

Also how would I incorporate a font colour change.
i.e. White for the darker Formatting.

Thanks in advance

 

[Norie]

The code doesn't go in a standard module it goes in the worksheet module.

That can be easily be accessed by right clicking the sheet tab and selecting View Code.

By the way what version of Excel are you using?

 

[Tomred]

Hi Norie
Thanks for the reply.

I can never work out the correct place to put the code.
The worksheet is the most obvious place, and the last for me to put the code.... on your prompt too!!....thanks.

The code works fine now.
Can you explain how the formatting identifies the option selected in the validation list, and how I could incorporate code to change the font to white for darker colours.

I purchased the Mr Excel VBA & Macros book a while ago, although I understand what is explained and the examples used, I do have a difficulty when it comes to working out something of pracical use to me...if you know what I mean.

I record a macro, investigate the recorded code, and then try to follow the 10 rules, but get lost in the realms of code when nothing works out for me!!

Possibly it's my approach to learning VBA, think of something I want to do like the above.....but the provided code is so different from what is created by using the macro recorder and reviewing the code.

I am using Excel 2003

If posting code: use code tags.....What are code tags?

Any suggestions would be appreciate?

 

[Norie]

The formatting doesn't identify the option selected.

The code identifies it and then applies the appropriate format.

As to using the macro recorder - that's probably the best way to learn.

Sure sometimes the code generated is a mess but it's normally not too hard to clean up.

I still use the macro recorder to get the correct syntax for various methods when I can't recall them of the top of my head, which is often.

 

[Tomred]

Thanks for the comfirmation Norie,

I just need to persevere, read the book some more and be patient, learn the quirks of the maco recorder and hopefully it will click soon.

Cheers

 

[mikerickson]

I'm glad the code worked for you.

Norie, thanks for filling in the gaps in my post.

 

[Tomred]

I have been trying to incorportate code to allow me to determine font colour and with repect to the backgroung colour.

White font with dark backgroung and Black font with Lighter coloured back grounds.

Irrespective of how I attempt to abreviate the macro recording, it proves to be unsuccessful.

Thanks

Private Sub Worksheet_Change(ByVal Target As Excel.Range)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
If Target.Cells.Count = 1 And Target.Column = 8 And Target.Row < 16 Then<o:p></o:p>
<o:p></o:p>
With Target.EntireRow.Range("a1:g1")<o:p></o:p>
<o:p></o:p>
Select Case LCase(CStr(Target.Value))<o:p></o:p>
Case Is = "black"<o:p></o:p>
.Font.ColorIndex = 2<o:p></o:p>
.ColorIndex = 2<o:p></o:p>
.Interior.ColorIndex = 1<o:p></o:p>
Case Is = "red"<o:p></o:p>
.Font.ColorIndex = 1<o:p></o:p>
.Interior.ColorIndex = 3<o:p></o:p>
Case Is = "blue"<o:p></o:p>
.Font.ColorIndex = 2<o:p></o:p>
.Interior.ColorIndex = 5<o:p></o:p>
Case Is = "pink"<o:p></o:p>
.Font.ColorIndex = 1<o:p></o:p>
.Interior.ColorIndex = 7<o:p></o:p>
Case Else<o:p></o:p>
.Interior.ColorIndex = xlNone<o:p></o:p>
End Select<o:p></o:p>
<o:p></o:p>
End With<o:p></o:p>
End If<o:p></o:p>
End Sub