Conditional Macro in Excel 2

[dpgirl]

I would like to be able to do some conditional formatting in Excel, but I'd like to do it through a Macro since this will give me the flexibility to have as many condition statements as I want. Can someone help me with the VB script?

Here is what I want to do through the macro.

1) Look at all values in column D
2) If any of the values equals "John", then shade the corresponding cell blue.
3) If any of the values equals "Mary", then shade the corresponding cell red.

Thanks!

 

[longhair]

dpgirl,
tools -> macro -> record new macro
do some formatting and then stop recording.
look at the code generated and change as needed.
regards,
longhair

 

[AnotherHiggins]

Something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Set FormatRange = Range("D1:D1000")
For Each Cell In FormatRange
  With Cell
    Select Case Cell.Value
      Case Is = "John"
        .Interior.Color = vbBlue
      Case Is = "Mary"
        .Interior.Color = vbRed
      Case Is = "OtherName"
        .Interior.Color = vbYellow
      '...
      '...
      '...
    End Select
  End With
Next Cell
End Sub

This code goes in the worksheet, not a regular module.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[firefytr]

If you only want those cells to change when/if you change the value(s) in that range then I would recommend adding some lines to John's code ...

Private Sub Worksheet_Change(ByVal Target As Range)
Set FormatRange = Range("D1:D1000")
If Target.Cells.count > 1 then exit sub
If Intersect(target, Formatrange) is nothing then exit sub
For Each Cell In FormatRange

HTH

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci

 

[dpgirl]

Thank you John and Zack. I have a stupid question. What does it mean to put the code in the worksheet and not a module? I have never done anything like this before.

 

[firefytr]

Most worksheet code the worksheet modules hold are for worksheet change events. ActiveX controls code (from the Controls Toolbox) are also stored there, as they are objects of the worksheet as well. These change events are specific to the sheets module that the code/event is stored in. There is also a ThisWorkbook module that holds workbook events for the workbook (not worksheet); you can also hold worksheet events here that apply to all worksheets. By default, all events in these modules are declared as Private.

HTH

-----------
Regards,
Zack Barresse

Simplicity is the ultimate sophistication.
- Leonardo da Vinci