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

 

[Tomred]

Sorry about the poor post formatting above.

I tried to edit it to make it more presentable, didn't realise there was a time limit, got distracted and ran out of time!

It won't happen again

 

[Tomred]

Thanks - I couldn't spot my problem for looking at it.

Success at last...........Thanks MikeRickson for the most of it.

My working code is as below, if anyone thinks I could improve the code please contribute as I want to improve, a beginners plea for advice!!Thanks in advance:

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"
                .Font.ColorIndex = 2
                .Interior.ColorIndex = 1
            Case Is = "red"
                .Font.ColorIndex = 1
                .Interior.ColorIndex = 3
            Case Is = "blue"
                .Font.ColorIndex = 2
                .Interior.ColorIndex = 5
            Case Is = "pink"
                .Font.ColorIndex = 1
                .Interior.ColorIndex = 7
            Case Else
                .Interior.ColorIndex = xlNone
        End Select
    End With
End If
End Sub

 

[Norie]

Re: Thanks - I couldn't spot my problem for looking at it.

I don't know if this would be regarded as an improvement but it should work.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim iIntClr As Long
Dim iFontClr As Long
    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"
                    iFontClr = 2
                    iIntClr = 1
                Case Is = "red"
                    iFontClr = 1
                    iIntClr = 3
                Case Is = "blue"
                    iFontClr = 2
                    iIntClr = 5
                Case Is = "pink"
                    iFontClr = 1
                    iIntClr = 7
                Case Else
                    iIntClr = xlNone
                    iFontClr = 0
            End Select
            
                .Font.ColorIndex = iFontClr
                .Interior.ColorIndex = iIntClr
                
        End With
                    
    End If
End Sub

 

[Tomred]

Hi Norie,

Thanks for the code.... I'll give it a try.........

Taking a view of both versions of code....please clarify the following....

Question 1
I assume, .Font.ColorIndex = iFontClr and .Interior.ColorIndex = iIntClr are variables....... iIntClr and iFontClr?

Question 2
I thought variables had to be defined at the beginning of code, or am I wrong?

Question 3
If Target.Cells.Count = 1 And Target.Column = 8 And Target.Row < 16 Then

Can you explain how the above code reads..........
The only part I understand is . Row less than 16................what is the rest of the statement doing?

Thanks

 

[Norie]

1 Yes they are variables.

2 They are declared.

Dim iIntClr As Long
Dim iFontClr As Long

3 This part checks that only 1 cell has changed.

Target.Cells.Count = 1

This part checks the cell that has been changed is in column 8.

Target.Column = 8

This part checks that the cell that has changed is in the first 15 rows.

Target.Row<16

 

[Tomred]

Thanks again Norie,

One last question regarding something else that is confusing me.

I am trying to understand the flow of the code..............

With
 
   Case Select
 
       Case 1
       Case 2
 
         End Select
 
              'Variables below.....can these be placed immediately after the      
               declaration?
 
                .Font.ColorIndex = iFontClr
                .Interior.ColorIndex = iIntClr
 
        End With

Confirm 1
Am I correct to say that this code is using a With...End With Statement.......nesting a Select Case Statement?

Confirm 2
Each select case looks beyond "End Select" to find the variable value?

Thanks for your patience, all is helpful to me

 

[Norie]

1 Yes it is using a With End, but it's not really 'nesting' the Select Case. That just happens to be inside it.

2 Don't know what you mean there.

 

[Tomred]

Hi Norie,

To follow your prevous answers:

Question 1...followup
If it's not "Nesting" how would you define nesting code?

Question 2...followup
I didn't think I explained myself well, to clarify .....

I am trying to understand the " top to bottom" sequence of the code as it runs.............and I am not sure I understand how the code after the "End Select" fits in to the sequence? Code shown below:

Also can this code be placed immediately after the declaration?

.Font.ColorIndex = iFontClr
.Interior.ColorIndex = iIntClr

 

[Norie]

Sorry but I don't really understand your questions.

1 I wouldn't call placing code within a With End With structure as 'nesting', but I could be wrong.

Why do you ask anyway?

2 Don't know why you don't understand the 'top to bottom', a select case just works that way.
If a match is made it executes the code in that case and then ends the Select.

 

[Tomred]

Hi Norie,

Thanks again.....I have a working code now.

I just like to know why things are done a certain way, as compared to just accepting them.......record a macro, edit it , often results in code I just don't understand.....although it works....it would be nice to know why.

I understatnd the case select statement option.....I just don't understand how it knows to use the variables afer the End Select.

I have started the VBA and Macros for MS excel from the beginning...maybe I am running before I can walk.

Thanks again