I am working on a calendar that uses 3 colors to identify a day of vacation
(VA), personal holiday (PH) and a half-day of vacation (HDVA).

The user changes the cell color based upon the above conditions. VA =
colorindex 4; PH = colorindex 6; and HDVA = colorindex 8.

Should a user wish to clear a VA, PH, or HDVA they run a macro ClearVA_PH ()

The source code is:

If (Selection.Interior.ColorIndex = 4 Or Selection.Interior.ColorIndex = 8
Or...) Then
Selection.Interior.ColorIndex = xlNone
Else: MsgBox "Please select a cell that contains a formatted VA or PH"
End If

My problem lies in the event that if the user selections multiple cells of
different vacation types (VA and PH) I get the error message.

I wish to allow the user to select multiple cells of different vacation
types. The code works for multiple cells of the same type, but not different
types.

Is there an efficient way to do this without lengthy If Then Else statements
to cover all possibilities?

Any assistance would be greatly appreciated.

Scotty9349

Hi Scotty,

Two examples that will process the cells that meet the conditions but ignore
the ones that do not. However, if none of the selections meet the criteria
then a message.

Sub test()
Dim cel As Range
Dim validSelect As Boolean

For Each cel In Selection
If (cel.Interior.ColorIndex = 4 Or _
cel.Interior.ColorIndex = 8) Then
validSelect = True
cel.Interior.ColorIndex = xlNone
End If
Next cel

If validSelect = False Then
MsgBox "Please select a cell that contains a formatted VA or PH"
End If

End Sub

I actually like to use Select case in lieu of multiple Or's because you can
just add the values to the Case separated by a comma like the following.
Other than that works as previous example.

Sub test2()
Dim cel As Range
Dim validSelect As Boolean

For Each cel In Selection
Select Case cel.Interior.ColorIndex
Case 4, 8
validSelect = True
cel.Interior.ColorIndex = xlNone
End Select
Next cel

If validSelect = False Then
MsgBox "Please select a cell that contains a formatted VA or PH"
End If

End Sub


--
Regards,

OssieMac