Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel VBA Macro Multiple If/Else Statements (2003)

    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.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA Macro Multiple If/Else Statements (2003)

    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).

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Macro Multiple If/Else Statements (2003)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA Macro Multiple If/Else Statements (2003)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Macro Multiple If/Else Statements (2003)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA Macro Multiple If/Else Statements (2003)

    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)

  7. #7
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Macro Multiple If/Else Statements (2003)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •