Conditional formatting using macros

Closed
-
 Sats -
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,

4 replies

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

Yes, i got my desired output.

Thanks a lot for all this help.

Best Regards,
Satyendra
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
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
Hi Ivan,

I used this program to solve mz problem:

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

can you please modify this program , I want use some Text value in stead coloring the column.

For eg: i want

Case Is = "A"
Set MyRange = .Range("B1:C1")
MyColor = 65535 instead color i want MyColor=abc value in column ........like this

regards,
sats
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
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