Rx_
Nothing In Moderation
- Local time
- Yesterday, 20:34
- 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?
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.
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.