VBA: Help with IF-THEN-ELSE Statement

[zero269]

Hello,

I'm trying to use an IF-Then-Else Statement, but no matter what I try, I can't ever get it to work. It seems to hate me...

I've used two examples online. As written, they work using their own sample data. But if I make even one simple change, it fails, and I can't figure it out.

Any help with identifying where I'm going wrong would be greatly appreciated.

I tried stepping through the code in a standard Sub as well, but it was a NO GO. The purpose was to rule out any potential issues with using the Worksheet_SelectionChange considering I want the conditional formatting to be applied as scores are either being entered or updated.

The following code is based on the following YouTube video:

'Conditionally Format Scores when values are entered
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim cell As Range
    For Each cell In Range("tblScores[Score]")
        If cell.Value > 0 And cell.Value <= 0.6 Then
            cell.DisplayFormat.Interior.Color = 13551615 'light red
        ElseIf cell.Value > 0.6 And cell.Value <= 0.8 Then
            cell.DisplayFormat.Interior.Color = 10086143 'light yellow
        ElseIf cell.Value > 0.8 And cell.Value < 1 Then
            cell.DisplayFormat.Interior.Color = 11389944 'light orange
        ElseIf cell.Value = 1 Then
            cell.DisplayFormat.Interior.Color = 11854022 'light green
        Else
            cell.DisplayFormat.Interior.Color = 16777215 'default: no color
        End If
    Next cell

End Sub

Following along from this video:

 

[Skyybot]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim cell As Range
    For Each cell In Target
        If cell.Value > 0 And cell.Value <= 0.6 Then
            cell.DisplayFormat.Interior.Color = 13551615 'light red
        ElseIf cell.Value > 0.6 And cell.Value <= 0.8 Then
            cell.DisplayFormat.Interior.Color = 10086143 'light yellow
        ElseIf cell.Value > 0.8 And cell.Value < 1 Then
            cell.DisplayFormat.Interior.Color = 11389944 'light orange
        ElseIf cell.Value = 1 Then
            cell.DisplayFormat.Interior.Color = 11854022 'light green
        Else
            cell.DisplayFormat.Interior.Color = 16777215 'default: no color
        End If
    Next cell

End Sub

 

[Skyybot]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim cell As Range
    For Each cell In Target
        If cell.Value > 0 And cell.Value <= 0.6 Then
            cell.DisplayFormat.Interior.Color = 13551615 'light red
        ElseIf cell.Value > 0.6 And cell.Value <= 0.8 Then
            cell.DisplayFormat.Interior.Color = 10086143 'light yellow
        ElseIf cell.Value > 0.8 And cell.Value < 1 Then
            cell.DisplayFormat.Interior.Color = 11389944 'light orange
        ElseIf cell.Value = 1 Then
            cell.DisplayFormat.Interior.Color = 11854022 'light green
        Else
            cell.DisplayFormat.Interior.Color = 16777215 'default: no color
        End If
    Next cell

End Sub

This works for any highlighted range

 

[Joe4]

A bunch of comments/questions:

Note that "Worksheet_SelectionChange" runs any time a cell is selected.
If you want it to run whenever a cell is manually updated, use "Worksheet_Change" instead.

How exactly is your data being changed?

And do you really want this code to apply to every cell on your sheet, or just a certain range?

Why do you need VBA code instead of just using Conditional Formatting?

 

[zero269]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim cell As Range
    For Each cell In Target
        If cell.Value > 0 And cell.Value <= 0.6 Then
            cell.DisplayFormat.Interior.Color = 13551615 'light red
        ElseIf cell.Value > 0.6 And cell.Value <= 0.8 Then
            cell.DisplayFormat.Interior.Color = 10086143 'light yellow
        ElseIf cell.Value > 0.8 And cell.Value < 1 Then
            cell.DisplayFormat.Interior.Color = 11389944 'light orange
        ElseIf cell.Value = 1 Then
            cell.DisplayFormat.Interior.Color = 11854022 'light green
        Else
            cell.DisplayFormat.Interior.Color = 16777215 'default: no color
        End If
    Next cell

End Sub

Hi Skyybot,

Thanks for taking a look at this.

At first glance, I wasn't sure what changes were made. However, I noticed you are using Target in place of Range("tblScores[Score]").
I tested it out but it's not liking my Else Statement:

I drilled down into the Locals Windows, and I noticed that there is a different value for DisplayFormat.Interior.Color. I'm not sure if that's a concern considering a new value is supposed to be applied.

One thing I forgot to add earlier was my sample data for reference... if that helps.

VBA Testing.xlsm
	A	B	C	D	E	F
1	Score	Student		Condition	DisplayFormat Interior.Color	HEX/RGB Color Values
2	100%	Student		Cell value < 0.6	13551615	HEX #FFC7CE / RGB 255,199,206
3	80%	Student		Cell value < 0.8	10086143	HEX #FFE699 / RGB 255,230,153
4	60%	Student		Cell value < 1	11389944	HEX #F8CBAD / RGB 248,203,173
5	40%	Student		Cell value = 1	11854022	HEX #C6E0B4 / RGB 198,224,180
6	20%	Student		Cell value is ""	16777215	Default value for no color
7		Student
CF_Scores

 

[zero269]

Note that "Worksheet_SelectionChange" runs any time a cell is selected.
If you want it to run whenever a cell is manually updated, use "Worksheet_Change" instead.

Hi Joe,
I took your advice and tested the suggested code using both methods. Unfortunately, the code was still giving me problems.
I'm not really sure what method will work best at this time until I can see it in action.

How exactly is your data being changed?

And do you really want this code to apply to every cell on your sheet, or just a certain range?

My data is being changed by manually entering the scores.
I want the code to be isolated to just the Score column if possible.

Why do you need VBA code instead of just using Conditional Formatting?

I'm currently using Conditional Formatting. I'm looking into VBA as an alternate solution.
I figured the best way to learn VBA is to use it to perform tasks I'm currently using.
Conditional Formatting can get out of hand, so I'm looking to convert some of my CF Rules to VBA to thin the CF herd...
I think the CF Rules that I use that point to Named Ranges (lists) will be where I see the most benefit. All of my data are in Tables. Having to create a Named Range as well to use with CF, I think the VBA approach will be easier considering I can reference a Table Column without having to create a Named Range in the Name Manager.

 

[zero269]

UPDATE:

I finally figured out the root problem to the VBA code I provided.
It appears that the DisplayFormat is a Read Only property which explains why it can't be used to set the Interior Color. It can only be used to read the Interior Color.

When I changed the code from:

DisplayFormat.Interior.Color

To:

Interior.Color

It worked!

So, the moral to my story is that:

Range.DisplayFormat.Interior.Color is used to read the color value.
Range.Interior.Color is used to set the color value.

Resources:

Range.DisplayFormat property

Range.DisplayFormat property (Excel)

Office VBA reference topic

learn.microsoft.com

Returns a DisplayFormat object that represents the display settings for the specified range. Read-only.

DisplayFormat object

DisplayFormat object (Excel)

Office VBA reference topic

learn.microsoft.com

Represents the display settings for an associated Range object. Read-only.

Remarks
Actions such as changing the conditional formatting or table style of a range can cause what is displayed in the current user interface to be inconsistent with the values in the corresponding properties of the Range object. Use the properties of the DisplayFormat object to return the values as they are displayed in the current user interface.


Working Code:

Sub SetInteriorColor()
    
    Dim cell As Range
    
    For Each cell In Range("tblScores[Score]")
        If cell.Value > 0 And cell.Value <= 0.6 Then
            cell.Interior.Color = 13551615 'light red
        ElseIf cell.Value > 0.6 And cell.Value <= 0.8 Then
            cell.Interior.Color = 10086143 'light yellow
        ElseIf cell.Value > 0.8 And cell.Value < 1 Then
            cell.Interior.Color = 11389944 'light orange
        ElseIf cell.Value = 1 Then
            cell.Interior.Color = 11854022 'light green
        Else
            cell.Interior.Color = 16777215 'default: no color
        End If
    Next cell
    
End Sub

VBA Testing.xlsm
	A	B	C	D	E	F
1	Score	Student		Condition	DisplayFormat Interior.Color	HEX/RGB Color Values
2	100%	Student		Cell value < 0.6	13551615	HEX #FFC7CE / RGB 255,199,206
3	80%	Student		Cell value < 0.8	10086143	HEX #FFE699 / RGB 255,230,153
4	60%	Student		Cell value < 1	11389944	HEX #F8CBAD / RGB 248,203,173
5	40%	Student		Cell value = 1	11854022	HEX #C6E0B4 / RGB 198,224,180
6	20%	Student		Cell value is ""	16777215	Default value for no color
7		Student
CF_Scores

One thing to note about the working code above, is that this highlights the cells the way the Fill Color tool does; meaning it can be cleared easily.
Originally, I was looking to conditionally format the cells using VBA as an alternative to the Conditional Formatting Rules Manager. After spending quite, a bit of time on this today, I've discovered that it's not possible... the way I was thinking it was.

Turns out you can use VBA to ADD conditional formatting to the Conditional Formatting Rules Manager (CFRM), but can't use VBA to replace it.
Once I figured out how it works, I was surprised when I went into the CFRM,I found my CF Rules added back in using the VBA code I created:

Sub AddFormatConditionColor()

    Dim Score As Range
    Set Score = ActiveSheet.Range("tblScores[Score]")
    
    With Score
        .FormatConditions.Delete
        
        'Cell value is BLANK (Stop if True)
        .FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(TRIM(A2))=0"
        .FormatConditions(1).StopIfTrue = True
        
        'Cell value < 60%
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0.6"
        .FormatConditions(2).Interior.Color = 13551615
        
        'Cell value < 80%
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0.8"
        .FormatConditions(3).Interior.Color = 10086143

        'Cell value < 100%
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=1"
        .FormatConditions(4).Interior.Color = 11389944

        'Cell value = 100%
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=1"
        .FormatConditions(5).Interior.Color = 11854022

    End With

End Sub

I thought this was a loss, but realized I can use this to back up my CF Rules in the event I need to quickly re-add them when testing new CF Rules to replace the old ones.
Once the Format Conditions are added, there's no need to run the code again, unless you've made changes to it. Assuming you delete the old one first (FormatConditions.Delete).

Thanks to those of you that provided assistance with this journey today!

This is certainly a very useful feature...

VBA Testing.xlsm
	A	B
1	Score	Student
2	100%	Student
3	80%	Student
4	60%	Student
5	40%	Student
6	20%	Student
7		Student
CF_Scores

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A7	Expression	=LEN(TRIM(A2))=0	text	YES
A2:A7	Cell Value	<0.6	text	YES
A2:A7	Cell Value	<0.8	text	YES
A2:A7	Cell Value	<1	text	YES
A2:A7	Cell Value	=1	text	YES

 

[zero269]

Just an FYI for those who stumble on to this thread...

Found a better way to add a Conditional Formatting Rule when a "Cell contains a blank value"
From :

.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(TRIM(A2))=0"

To:

.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="="""""

Provided by @RoryA in this thread:

VBA conditional formatting: Cell contains a blank value

Hi everyone, I have a VBA code that clears conditional formats and then enters them back in for when fellow employees jumble them up so to speak. The only conditional format I have issues with is the one that states: Cell contains a blank value. I do not know how to write this in VBA Here is...

www.mrexcel.com

 

[zero269]

Found a better way to add a Conditional Formatting Rule when a "Cell contains a blank value"

an even better way considering it's being added using the same criteria as the CFR Manager:

.FormatConditions.Add Type:=xlBlanksCondition

To move it to the top of the list which is usually where you want it:

.FormatConditions.Add Type:=xlBlanksCondition
.FormatConditions(.FormatConditions.Count).SetFirstPriority

No need to add StopIfTrue = True because that's the default when adding CF Rules with VBA, versus in the CFRM.

.FormatConditions(.FormatConditions.Count).StopIfTrue = True