Changing colour on changing a Data Validation List added with VBA

**e4excel** · 08-12-2011, 04:12 PM

Changing colour on changing a Data Validation List with VBA

Dear Forum,

As per the Title, I am Adding Data Validation using a Macro but I want to also change the colour on selecting the Items in the desired column F.


Sub AddDV()
'http://www.excelforum.com/newreply.php?do=newreply&noquote=1&p=2319790
Dim Target As Range
Dim LastRow As Long

    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
    End With

For Each cell In Range("C2", Range("C65536").End(xlUp))
  'Set target = Range("B1:B3")
  Set Target = Range(cell.Offset(0, 3), cell.Offset(LastRow, 3))
  
  With Target.Validation
        .Delete
        .Add xlValidateList, 1, 1, "Fixed Cost,Variable Cost"
        .InCellDropdown = True
        .ShowInput = True
  End With

Next
End Sub

I am getting 90 filled rows instead of 45 in the column F and 45 in Column G when I am running the same code again for the second column G

I want to add Data Validation Items such as :1 Fixed Cost which should be in the Red Colour i.e. Colour Index = 3
2 Variable Cost which should be in the Blue Colour i.e. Colour Index = - 4165632

The Text should have the below Formatting
Horizontal Alignment= Left
Font = "Book Antiqua"
Font Size = 13

I want to get the colour change immediately on changing the Data Validation...

Warm Regards
e4excel

**davesexcel** · 08-12-2011, 08:23 PM

You need to place code in the worksheet_change event to change the color of the cell.
The row issue is hard to describe as you have not supplied code that shows what is going on.
Maybe supply a very simple workbook showing what you are asking.

**e4excel** · 08-13-2011, 07:31 AM

Ok as you say DavesExcel,

I will be uploading an attachment...
I think I require some tweak in the Data Validation as well as something going wrong somewhere, as I have taken this code from the net I am nost sure of repairing the same..

So, pleae help me over two things :
1. Getting the DataValidation through a Macro and also till only the last filled row in COlumn E...

2. Then I need the colours to change on changing the values from the list..

I know I can do it simply through the regular Data Validation and also can change colours through conditional formatting however, I want to learn VBA....

Please take the range from the Column J i.e J2:J3 for the DV in Col F &
and the range from the Column K i.e K2:K4 for the DV in Col G..

1 Fixed Cost which should be in the Red Colour i.e. Colour Index = 3
2 Variable Cost which should be in the Blue Colour i.e. Colour Index = - 4165632

The Text should have the below Formatting
Horizontal Alignment= Left
Font = "Book Antiqua"
Font Size = 13

I want to get the colour change immediately on changing the Data Validation...

Warm Regards
e4excel

**e4excel** · 08-13-2011, 10:57 AM

I found an useful link to change the Font Colour..

http://www.excelforum.com/excel-prog...using-vba.html

**e4excel** · 08-13-2011, 11:17 AM

Please help me with the Data Validation as that is happening erroneously for 90 rows instead of just 45..

For reference my trial and error modified code:


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim FColor As Integer

    If Not Intersect(Target, Range("F2:F20000")) Is Nothing Then
    
    On Error Resume Next
    
    
      Select Case Target
            Case "Fixed Cost"
                FColor = 3
            Case "Variable Cost"
                FColor = 41  '-4165632
            Case Else
                'Whatever
        End Select
        Target.Font.ColorIndex = FColor
    End If
    
Exit Sub
    
End Sub