Excel - CopyFromRecordset - Set Rules for values (1 Viewer)

Rx_

Nothing In Moderation
Local time
Today, 11:08
Joined
Oct 22, 2009
Messages
2,803
The Access Query (SQL Statement) bring over a group of columns that show up as a check box in Access. They show up as True / False in the Excel sheet.
This code selects the number of headers (columns) and number of rows returned by the recordset. Selects that area and applies the format rule. The True are formatted Green and the False are formatted Red.

? What does this mean? FormatConditions(1).StopIfTrue = False
It works great, but I can't find a good reference to what it means.
All code samples use the (1) and =False, just wondering if there are options?

Code:
   ' intRowPos is 5 - the data starts at row 5 to allow for a title bar
    objxl.DisplayAlerts = False     ' Turn off Display Alerts
    objxl.Worksheets(intWorksheetNum).Cells(intRowPos, 1).CopyFromRecordset rsDataRE_Seg1Rule
 
    intMaxheaderColCount = rsDataRE_Seg1Rule.Fields.Count - 1
 
    ' use recordset object to capture intMaxRecordCount. IntRowPos is a rownumber where the data begins.
    objxl.Range(Cells(intRowPos, 1), Cells(intMaxRecordCount + (intRowPos), intMaxheaderColCount + 1)).Select
    ' SPECIAL SECTION turns True Green and False to RED - using SELECTION
 
    objxl.Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=FALSE"
    objxl.Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With objxl.Selection.FormatConditions(1).Font
        .Color = -16383844
    End With
    With objxl.Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
    End With
    objxl.Selection.FormatConditions(1).StopIfTrue = False
    objxl.Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=TRUE"
    objxl.Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With objxl.Selection.FormatConditions(1).Font
        .Color = -16752384
    End With
    With objxl.Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13561798
    End With
    objxl.Selection.FormatConditions(1).StopIfTrue = False

B.T.W. from an error, when using this code it is important to apply formats in this order for Excel 2010 and Excel 2013. Any other order and the error is not all that descriptive.
1. Set the cell background. (.Interior.ColorIndex)
2. Set the properties of the font. (.Font.Bold)
3. Set the color of thefont. (.Font.Color)

Another reference indicates that no more than 3 formats can be applied to a range.
I am also looking for one that would leave BLANK cells unchanged after these two are applied.
 

Rx_

Nothing In Moderation
Local time
Today, 11:08
Joined
Oct 22, 2009
Messages
2,803
FormatConditions(index), where index is the index number of the conditional format, to return
Any range of cells can only have a max of three of these conditional formats.
This is a method to return the 1 to 3 formats.
My code where each of the two are indexed (1) works. Still not sure why the code works when both of the index are (1).
 

Rx_

Nothing In Moderation
Local time
Today, 11:08
Joined
Oct 22, 2009
Messages
2,803
Got an update from Mr Excel site. This is worth noting since I could not find any documentation anywhere else.
Code:
You can apply more than one lot of Conditional Formatting to a range.

For instance, you could have one that highlights the top 10 values in red and another that highhlights the numbers over 20 in amber.

The "stop if true" option means stop applying the conditional formatting if one is true. This is a good example of how it can be used: [URL="http://datapigtechnologies.com/blog/index.php/using-stop-if-true-when-conditional-formatting/"][COLOR=#0066cc]Using Stop If True when Conditional Formatting » Bacon Bits[/COLOR][/URL]
So, the order of the statements can make a big difference if there are greater than statements for example.
The macro recorder always chooses TRUE. However, for more complex applications, these logic choices can be applied as necessary.

Office 2013 and later
https://msdn.microsoft.com/en-us/library/office/ff839240.aspx
https://msdn.microsoft.com/en-us/library/office/ff820966.aspx
 
Last edited:

Users who are viewing this thread

Top Bottom