Conditional Formatting with VBA and xlExpression Formula1: with VBA Variables and Text

E

eric-apfel

New Member
Joined
Feb 22, 2013
Messages
3
Hello Mr. Excel!

I want to start by saying a quick thank you! I love this site.

I have tried to find answers in previous posts but I haven't found anything to find material that applies directly here, yet.

I've been using VBA to new worksheets that compile information from a "Source" worksheet. For each row on "Source", the code creates a new sheet. The code also creates a "Menu" sheet with summary information and hyperlinks to each worksheet (making it relatively quick to jump around in a 500+ worksheet environment). On this "Menu" sheet, some of the summary data is formatted using conditional formatting, corresponding to certain values.

I am working with the following code to format a range of cells brace thyselves:
Code: 
Option Explicit
Public Sht_Name As Object
'I used Option Explicit to make sure that Sht_Name could be referenced by several parts of the code.

Dim Call_Range As Range

       '... much further down

'The following is the beginning of the For Next Loop that creates the "NewSheets"
Set Call_Range = Sheets("Source").Range("A8:A507")
    For Each Sht_Name In Call_Range
        On Error Resume Next
        If Sht_Name <> Empty Then
        Call Create_NewSheet

        'The following applies conditional formatting to the Status column in the Menu
        With Sheets("Menu").Range("B" & Sheets("Source").Range(Sht_Name.Address(False, False)).Value + 1 & ":" & "F" & Sheets("Source").Range(Sht_Name.Address(False, False)).Value + 1)
                .FormatConditions.Add Type:=xlExpression, Formula1:="=F" & (Sheets("Source").Range(Sht_Name.Address(False, False)).Value + 1) & "=" & "Not contacted"
                .FormatConditions.Add Type:=xlExpression, Formula1:="=F" & (Sheets("Source").Range(Sht_Name.Address(False, False)).Value + 1) & "=" & "Voicemail"
                .FormatConditions.Add Type:=xlExpression, Formula1:="=F" & (Sheets("Source").Range(Sht_Name.Address(False, False)).Value + 1) & "=" & "Message with secretary"
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="Marketing materials sent"
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="Pitch scheduled"
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="In progress"
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="Rejected"
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="Closed"
                .FormatConditions(1).StopIfTrue = False
                .FormatConditions(1).Interior.Color = RGB(255, 255, 255)
                .FormatConditions(2).StopIfTrue = False
                .FormatConditions(2).Interior.Color = RGB(172, 185, 202)
                .FormatConditions(3).StopIfTrue = False
                .FormatConditions(3).Interior.Color = RGB(172, 185, 202)
                .FormatConditions(4).StopIfTrue = False
                .FormatConditions(4).Interior.Color = RGB(172, 185, 202)
                .FormatConditions(5).StopIfTrue = False
                .FormatConditions(5).Interior.Color = RGB(255, 255, 102)
                .FormatConditions(6).StopIfTrue = False
                .FormatConditions(6).Interior.Color = RGB(255, 192, 0)
                .FormatConditions(7).StopIfTrue = False
                .FormatConditions(7).Interior.Color = RGB(89, 89, 89)
                .FormatConditions(8).StopIfTrue = False
                .FormatConditions(8).Interior.Color = RGB(0, 176, 80)
        End With

'... further down

        End If
    Next Sht_Name

What you're thinking: Why are there some FormatCondition.Add's where Type = xlExpression and some where Type = xlCellValue?

Reason: At first I wanted just the one cell to change color based on the value inside it, but now I want multiple cells "B2:F2" to change to the same color depending on the value of "F2", and all subsequent rows.

Observations: This expression works:
Code: 
                .FormatConditions.Add Type:=xlExpression, Formula1:="=F" & (Sheets("Source").Range(Sht_Name.Address(False, False)).Value + 1) & "=" & "Voicemail"

But the expressions immediately preceeding it and following it don't.

My questions for you:

  1. What's going on?
  2. How can I fix this?
  3. Is there a better way of doing this?

Very kind regards to any and all who are generous enough to help me!

Eric
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
eric-apfel said:
I am working with the following code to format a range of cells brace thyselves:
Click to expand...

Welcome to MrExcel, Eric, and thank you for the warning. :LOL:

This expression:

Code: 
Sheets("Source").Range(Sht_Name.Address(False, False)).Value + 1

can be simplified to:

Code: 
Sht_Name.Value+1

You might further clarify your code by saving that address of the conditional cell to a string variable...
Code: 
sAddress = CStr("F" & Sht_Name.Value + 1)

...so you can use expressions like this...
Code: 
  .FormatConditions.Add Type:=xlExpression, Formula1:="=" & sAddress & "=Not contacted"

I'm not completely following whether you want the Conditional Formatting(CF) rule to have relative or absolute reference to (cell F2 in your example). You could add the "$" if you need it to be absolute.

You are better off having a single rule "Apply to:" a range of multiple cells rather than having separate rules that each Apply to: one cell.

There's several other things that could be improved in your code, but it's probably best that you start with those suggestions and post your revised version if you'd like some additional help.
 
Upvote 0
Jerry Sullivan said:
Welcome to MrExcel, Eric, and thank you for the warning. :LOL:

This expression:

Code: 
Sheets("Source").Range(Sht_Name.Address(False, False)).Value + 1

can be simplified to:

Code: 
Sht_Name.Value+1

You might further clarify your code by saving that address of the conditional cell to a string variable...
Code: 
sAddress = CStr("F" & Sht_Name.Value + 1)

...so you can use expressions like this...
Code: 
  .FormatConditions.Add Type:=xlExpression, Formula1:="=" & sAddress & "=Not contacted"

I'm not completely following whether you want the Conditional Formatting(CF) rule to have relative or absolute reference to (cell F2 in your example). You could add the "$" if you need it to be absolute.

You are better off having a single rule "Apply to:" a range of multiple cells rather than having separate rules that each Apply to: one cell.

There's several other things that could be improved in your code, but it's probably best that you start with those suggestions and post your revised version if you'd like some additional help.
Click to expand...


Hi Jerry,

Thanks for the reply. I'm trying to work in your modifications and see if I can simplify my code a bit. While my code has gotten visibly shorter (and less confusing) my troubles remain unresolved.

Let me show you how I modified things and then I'll get to the problem:

Code: 
Option Explicit
Public Sht_Name As Object
Public sAddress As String
'Again, I used Option Explicit to make sure that Sht_Name and sAddress could be referenced by several parts of the code.


Dim Call_Range As Range


       '... much further down


'The following is the beginning of the For Next Loop that creates the "NewSheets"
Set Call_Range = Sheets("Source").Range("A8:A507")
    For Each Sht_Name In Call_Range
        On Error Resume Next
        If Sht_Name <> Empty Then
        Call Create_NewSheet

        'Here's I've added Jerry's idea: to show how I incorporated his code
        sAddress = (Sheets("Source").Range(Sht_Name.Address(False, False)) + 1)
        
        'The following applies conditional formatting to the Status column in the Menu
        With Sheets("Menu").Range("B" & sAddress & ":" & "F" & sAddress)
                .FormatConditions.Add Type:=xlExpression, Formula1:="=F" & (sAddress) & "=Not contacted"
                .FormatConditions.Add Type:=xlExpression, Formula1:="=F" & (sAddress) & "=Voicemail"
                .FormatConditions.Add Type:=xlExpression, Formula1:="=F" & (sAddress) & "=Message with secretary"
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="Marketing materials sent"
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="Pitch scheduled"
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="In progress"
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="Rejected"
                .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="Closed"
                .FormatConditions(1).StopIfTrue = False
                .FormatConditions(1).Interior.Color = RGB(255, 255, 255)
                .FormatConditions(2).StopIfTrue = False
                .FormatConditions(2).Interior.Color = RGB(172, 185, 202)
                .FormatConditions(3).StopIfTrue = False
                .FormatConditions(3).Interior.Color = RGB(172, 185, 202)
                .FormatConditions(4).StopIfTrue = False
                .FormatConditions(4).Interior.Color = RGB(172, 185, 202)
                .FormatConditions(5).StopIfTrue = False
                .FormatConditions(5).Interior.Color = RGB(255, 255, 102)
                .FormatConditions(6).StopIfTrue = False
                .FormatConditions(6).Interior.Color = RGB(255, 192, 0)
                .FormatConditions(7).StopIfTrue = False
                .FormatConditions(7).Interior.Color = RGB(89, 89, 89)
                .FormatConditions(8).StopIfTrue = False
                .FormatConditions(8).Interior.Color = RGB(0, 176, 80)
        End With

        End If
    Next Sht_Name

Problems:

1) The Sht_Name.Value + 1 trick actually doesn't seem to work. I also tried the bit with sAddress = CStr("F" & Sht_Name.Value + 1) and that didn't work either. (I'm using MS Excel 2010 FYI).

For some reason Sht_Name.Value + 1 doesn't seem to compute well (maybe because it's defined as an Object). However, referencing its full address I can save it as a string like so:

Code: 
sAddress = Sheets("Source").Range(Sht_Name.Address(False,False)) +1

That seems to take care of the computational bit which allows it to be plugged into my code which I've managed to have function partially by defining sAddress as a Public variable at the top.

The result of the modified code is that on the "Menu" worksheet, the cells that I want formatted with conditional formatting are formatted with the following formula: =F2=Voicemail which obviously doesn't work because it doesn't have the requisite quotation marks to indicate that the F2 should equal "Voicemail" i.e. =F2="Voicemail". Also, my other .FormatConditions.Add (s) still don't work. I determined that this is because of the spaces in the quotation marks i.e. "Not contacted" (one space) and "Message with Secretary" (2 spaces).

So the workaround is obvious: I can replace the spaces with underscores, remove them altogether, OR be anal and find another way to make my code input quotation marks.

This is what I would like to do. Jerry, do you have any ideas?

Alternatively, instead of having the text as part of the conditional formatting formula, I am thinking it might be possible to replace the explicit text with a cell reference to cells containing the appropriate text i.e. a legend:

List:
1Not Contacted
2Voicemail
3Message with Secretary

<tbody>
</tbody>

I'm gonna see if this works. But please let me know if you have a solution for getting the code to force those quotation marks into place!

Best regards,

Eric
 
Upvote 0
Eric, I won't have time to look at this in detail until tonight.

I didn't try to mock up your setup and test - so sorry that I had some errors in my suggested code.

I misread how you are trying to use Sht_Name (I missed that you are trying to read the value from Sheet Source). Go ahead and use your alternate version for now. Declare Sht_Name as a Range data type instead of an Object.

When you want to have literal quotes in a string, you can use a pair of quotes.
Code: 
Formula1:="=" & sAddress & "=""Not contacted"""

I'll be glad to assist more tonight.
 
Upvote 0
Hi Jerry,

Thanks for all your help. I wound up using my workaround by creating a legend.

I've tried using the double brackets in the past without success. I feel like excel reads them from left to right so ""Not Contacted"" would look like "Null"+Not Contacted+"Null" but I've been wrong before! I'll give it a shot.

Here's how the code looks now:

Code: 
Option Explicit
Public Sht_Name As Object
Public sAddress As String

'...further down...

Dim Call_Range As Range

Set Call_Range = Sheets("Source").Range("A8:A507")
    For Each Sht_Name In Call_Range
        On Error Resume Next
        If Sht_Name <> Empty Then
        Call Create_Dashboard

        'The following applies conditional formatting to the Status column in the Menu
        With Sheets("Menu").Range("B" & sAddress & ":" & "F" & sAddress)
                .FormatConditions.Add Type:=xlExpression, Formula1:="=$F$" & (sAddress) & "=$H$2"
                .FormatConditions.Add Type:=xlExpression, Formula1:="=$F$" & (sAddress) & "=$H$3"
                .FormatConditions.Add Type:=xlExpression, Formula1:="=$F$" & (sAddress) & "=$H$4"
                .FormatConditions.Add Type:=xlExpression, Formula1:="=$F$" & (sAddress) & "=$H$5"
                .FormatConditions.Add Type:=xlExpression, Formula1:="=$F$" & (sAddress) & "=$H$6"
                .FormatConditions.Add Type:=xlExpression, Formula1:="=$F$" & (sAddress) & "=$H$7"
                .FormatConditions.Add Type:=xlExpression, Formula1:="=$F$" & (sAddress) & "=$H$8"
                .FormatConditions.Add Type:=xlExpression, Formula1:="=$F$" & (sAddress) & "=$H$9"
                .FormatConditions(1).StopIfTrue = False
                .FormatConditions(1).Interior.Color = RGB(255, 255, 255)
                .FormatConditions(2).StopIfTrue = False
                .FormatConditions(2).Interior.Color = RGB(172, 185, 202)
                .FormatConditions(3).StopIfTrue = False
                .FormatConditions(3).Interior.Color = RGB(172, 185, 202)
                .FormatConditions(4).StopIfTrue = False
                .FormatConditions(4).Interior.Color = RGB(172, 185, 202)
                .FormatConditions(5).StopIfTrue = False
                .FormatConditions(5).Interior.Color = RGB(255, 255, 102)
                .FormatConditions(6).StopIfTrue = False
                .FormatConditions(6).Interior.Color = RGB(255, 192, 0)
                .FormatConditions(7).StopIfTrue = False
                .FormatConditions(7).Interior.Color = RGB(89, 89, 89)
                .FormatConditions(8).StopIfTrue = False
                .FormatConditions(8).Interior.Color = RGB(0, 176, 80)
        End With

        End If
    Next Sht_Name

It works but it's not perfect.

I will repost if I need more help but for now I am satisfied. Thank you very much Jerry for all your help! Happy Holidays!

Eric
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
VBA set conditional formatting application-defined or object-defined error
Replies
3
Views
75
Cubist
C
L
  • Question
Conditional Formatting with VBA
Replies
3
Views
176
skillilea
S
Nuke_It_Newport
  • Solved
Conditional Formatting Rules Not Applying
Replies
1
Views
685
Nuke_It_Newport
Nuke_It_Newport
T
  • Solved
VBA & Conditional Formatting
2
Replies
12
Views
578
MARK858
MARK858
C
  • Solved
Conditional Formatting Macro
Replies
2
Views
181
rlv01
R

Forum statistics

Threads
1,214,829
Messages
6,121,826
Members
449,051
Latest member
excelquestion515

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top