If then Statement (Excel 2003)

Topic

New Reply

2 Columns:

Col 1 is a dropdown:
Choices: Red / Green / Yellow

Col 2: Blank

I need the code to output:

If Col1 is Green, Format Col2 as RED(filled)

if Col1 = Red or Yellow: input “n/a” in Col2.

Thanks

Reply | Quote

Replies

Right-click the sheet tab and select View Code from the popup menu.
Create the following code in the module that is opened:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
If Not Intersect(Range(“A:A”), Target) Is Nothing Then
For Each oCell In Intersect(Range(“A:A”), Target)
Select Case oCell
Case “”
oCell.Offset(0, 1).Interior.ColorIndex = xlColorIndexNone
oCell.Offset(0, 1) = “”
Case “Green”
oCell.Offset(0, 1).Interior.Color = vbRed
oCell.Offset(0, 1) = “”
Case Else
oCell.Offset(0, 1).Interior.ColorIndex = xlColorIndexNone
oCell.Offset(0, 1) = “N/A”
End Select
Next oCell
End If
End Sub

This assumes that the columns you mention are A and B. If they are different, modify the code accordingly.

Reply | Quote

I will try it… Thanks HANS!!!!!

Reply | Quote

That worked… Awesome:

Another dilema:

When I open it, my Macro Security is set to High so it is Disabled. No problem, I selected Medium, where it asks me to “trust” it.

How can I make this a “trusted” script to avoid the security issues? If I email this to 100 people, I will have to have them change the Security and I want to AVOID this…

Thanks

Reply | Quote

You could sign the VBA code with a digital certificate. Each user will be asked once to trust the certificate. After that, the code will run even if macro security is set to High, without asking for macros to be enabled.

However, digital certificates aren’t cheap. You’ll have to decide for yourself whether you (or your employer) can afford to get one.

See About macro security – Excel for more info.

Reply | Quote