The condition should be =$M2="TRUE" with the quotation, however I have had problems typing that into VBA, any advice?
Note that TRUE could actually be two different things in Excel - there is the text entry "TRUE" and there is the boolean entry TRUE.
How do you know which one you have? Boolean entries are centered within the cell, by default, and text entries are left-justified in the cell, by default.
You could also check with an equation like this on some blank cell on your sheet.
If the word TRUE appears in M2, and that formula returns TRUE, then it is boolean. If it returns FALSE, then it is text.
Why is that important? When referring to text values, you need to use Text qualifiers (double-quotes). When referring to boolean values, you don't.
So if your data is boolean, then the way the formula is currently written is fine:
If it is Text we need to add text qualifiers. But since text qualifiers are used in creating the formula, it can get a little messy. I like to use CHR(34), which is the ASCII representation of a double-quote, when trying to write literal double-quotes into a formula in VBA.
So I would probably create that formula in a string variable like this:
' Create formula for text entry TRUE
Code:
Dim myFormula As String
myFormula = "=$M2=" & Chr(34) & "TRUE" & Chr(34)
and then use it in your Conditional Formatting line like this:
Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=[COLOR=#ff0000]myFormula[/COLOR]
The second problem is having the rule apply to the entire row if cell M# is TRUE, any suggestions for that?
Note how your formula is being applied to "Selection". That means it is applied to whatever range is selected when the code is run (normally, I don't like to leave it to chance and have my VBA figure out exactly which range to apply the code to). So, if you select complete rows instead of just column M, it will be applied to the whole row.
The key to getting it to work correctly is the way the range is referenced in your formula. Usually, the row and column references move as the formula moves across different cells in your range. That is, unless you lock them down with an absolute range reference. By placing a "$" in front of the row/column, it locks that part of the range down.
In your formula, it is front of the "M". That is what we want. As we apply it to different columns within our row, we want that reference to be "frozen" to column M. However, we do not want it in front of the row reference, as we want that reference to change rows as we move down rows.
See here for more on range references:
Relative And Absolute Range References
To dynamically select the range to apply this to (all rows with data in column M, starting on row 2), you can put this block before your first row of code:
Code:
Dim lastRow As Long
Dim myRange As Range
' Find last row in column M with data
lastRow = Cells(Rows.Count, "M").End(xlUp).Row
' Set range to be whole rows from row 2 down to last row
Set myRange = Rows("2:" & lastRow)
' Apply conditional formatting to defined range
myRange.Select
