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

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
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
I am working with the following code to format a range of cells brace thyselves:
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.
 

eric-apfel

New Member
Joined
Feb 22, 2013
Messages
3
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.

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
 

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
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.
 

eric-apfel

New Member
Joined
Feb 22, 2013
Messages
3
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
 

Forum statistics

Threads
1,081,657
Messages
5,360,291
Members
400,580
Latest member
Drexl88

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top