Conditional formatting using macros Closed

Question

I've got a worksheet and cell A1 to A100 consist of a drop down list which was created using data validation. When the user selects a value from the list, for that particular row, certain cells need to be filled with yellow. This is needed, because the worksheet consists a lot of fields to be filled in. to make it easy for the user to fill in the values, we need to highlight only the required cells based on the value the user selects from the drop down list.


Row 1 consists of fields
if "A" is selected from drop down list in cell A2, cell B2 and C2 needs to be filled with yellow
if "B" is selected from drop down list in cell A3, cell B3 & C3 & D3 needs to be filled with yellow

if nothing is selected, all the cells shouldnt be filled with color.

thanks,

Ivan-hoe · Answer

Hello, 
No need to use macros to do what you want to do. You can simply use Excel conditionnal formatting, with the formulas below : 
for columns B and C : =OR($A2="A";$A2="B")
for column D : =$A2="B"

Nevertheless if you want to use a macro, you can use the event Worksheet_Change to have your macro run automatically
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range, MyColor
    If Intersect(Range("A1:A100"), Target) Is Nothing Then Exit Sub
    With Target
        .Range("B1:D1").Interior.Color = xlNone
        Select Case .Value
            Case Is = "A"
                Set MyRange = .Range("B1:C1")
                MyColor = 65535
            Case Is = "B"
                Set MyRange = .Range("B1:D1")
                MyColor = 65535
            Case Else
                Set MyRange = .Range("B1:D1")
                MyColor = xlNone
        End Select
        MyRange.Interior.Color = MyColor
    End With
End Sub
Ivan

moses · Answer

sorry i didnt mention earlier, the drop down list contains more than 3 values which i believe conditional formatting will not take care of.

Ivan-hoe · Answer

Hello Sats, 
to insert some text in a cell, use its property Value
e.g. : 
Case Is = "A" 
   .range("B1").Value = "abc" 
Case Is = "B"
   .range("B1").Value = "pqr"
   .range("C1").Value = "xyz"
Ivan

Sats · Answer

Hello Ivan,

Yes, i got my desired output.

Thanks a lot for all this help.

Best Regards,
Satyendra

Conditional formatting using macros

4 responses

Similar discussions

Subscribe To Our Newsletter!