Excel VBA Macro Multiple If/Else Statements (2003)

Topic

New Reply

I have an attached spreadsheet that I inherited with existing values. I am trying to enhance it by adding data validation lists that work in concert with conditional formatting. I found some code that gets around the 3 criteria limit for conditional formatting, but can’t bring it home.

1 – I would like to code to auto fill the existing values based on the established criteria in the code.

2 – I would like the coded criteria to work with the data validation lists that users will enter data on (Columns D, F and I).

3 – I would like Columns E and J to auto calculate based on the formula I created and format to a color specified in the code. How should the default value of “#VALUE!” be recognized so that it autofills to a specified color?

I have written the following code on the Rating worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range(“B2:I9”)) Is Nothing Then
Select Case Target
Case 1 To 1
icolor = 35
Case 2 To 2
icolor = 6
Case 3 To 3
icolor = 44
Case 4 To 4
icolor = 3
Case “N/A”
icolor = 15
Case “?VALUE”
icolor = 75
Case “#VALUE!”
icolor = 12
Case “0”
icolor = 15
Case “LOW”
icolor = 35
Case “MEDIUM”
icolor = 6
Case “High”
icolor = 3
End Select
With Worksheets(“Rating”)
Target.Interior.ColorIndex = icolor
End With
End If

Dim myRange As Range
Set myRange = Worksheets(“Rating”).Range(“B2:I9”)
ActiveWorkbook.RefreshAll

End Sub

1 and LOW are meant to be the same color. So are 2 and MEDIUM and 4 and HIGH.

0 and N/A should be the same colors as well. 0 is a value that is auto-calculated based on the formula in Columns E and J.

Thanks for you help.

Reply | Quote

Replies

You must set the formatting for each cell in the intersection of the target and B2:I9 individually, instead of for the target or intersection as a whole.
ColorIndex can be a number from 1 to 56 (inclusive), so 75 is not valid.
Your code checks for value ?VALUE but the validation lists contain VALUE?
You cannot use SELECT CASE for “real” error values, you’ll have to handle those separately.

You could create the following macro in a standard module:

Sub ApplyFormat()
Dim iColor As Integer
Dim rCell As Range
For Each rCell In Range(“B2:J9”)
If IsError(rCell) Then
iColor = 12
Else
Select Case UCase(rCell.Value)
Case 1, “LOW”
iColor = 35
Case 2, “MEDIUM”
iColor = 6
Case 3
iColor = 44
Case 4, “HIGH”
iColor = 3
Case “N/A”, “0”
iColor = 15
Case “VALUE?”
iColor = 4
End Select
End If
rCell.Interior.ColorIndex = iColor
Next rCell
End Sub

Call it like this in the Worksheet_Change event procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(“B2:J9”)) Is Nothing Then
Call ApplyFormat
End If
End Sub

ApplyFormat will format all cells in B2:J9, including those with formulas. (It would also have been possible to use the Worksheet_Calculate event to format the cells with colors).

Reply | Quote

Thanks Hans. That worked rather well. Thank you for working well with my typos and errors.

One glich is arising. Column G is expressing behavior that I don’t want. It is formatting to the same color as the calculated value of Column E. The color format of Column G should always be white (or 2) unless it is populated with VALUE? or N/A.

How would I exclude certain columns from the range stated in the macros or list multiple ranges for this macros to affect?

Thanks.

Reply | Quote

You can use something like this:

For Each rCell In Union(Range(“B2:”F9”), Range(“H2:J9”)
‘ formatting code goes here
…
Next rCell
For Each rCell in Range(“G2:G9”)
‘ formatting code goes here
…
Next rCell

Or you could loop through the entire range and insert an If statement that checks the column of rCell.

Reply | Quote

The first one in your list seems to have worked, although there still seems to be color fills occurring in rather random places on the worksheet, but places that I can live with. We can chalk it up to a Microsoftism I guess.

As I tried to “Lock” certain cells to users and place the worksheet in ‘Protected’ status, the macros generates an error that needs to be debugged.

Is there a way to protect the worksheet and lock certain columns or cells without generating the macros error? Please advise.

Reply | Quote

Excel doesn’t color cells at random, there must be something that causes it – either conditional formatting or an error in the code.

You can specify what is allowed and what isn’t when you protect a sheet. If you allow cells to be formatted, the code should run OK even when the sheet is protected.
Alternatively, you can unprotect the sheet in code (ActiveSheet.Unprotect), then format cells, and finally re-protect the sheet (ActiveSheet.Protect)

Reply | Quote

The not about allowing cells to be formatted helped. I had Locked cells and in the Protection, I didn’t check Allow Format. That prevented the macros from doing it’s thing and causing the error.

Reply | Quote