More VB and Excel (Groan!)
-
Hi guys (again!) Some of you may have been folowing my escapades with the Excel Object model, and many thanks to those that have offered assistance! So today's problem is as follows.... Have a column containing dates (Action dates). Each value in the column can be in either one of three states based on the values in two other date cols( Start date and End date). The values can either be accepted - i.e. between the two other dates, and therefore green, Not accepted, i.e. outside the dates and therefore Red, or Default - a value read from a SProc in an SQLServer db, and hence Blue. Now using conditional formatting, can use xlCellValue type, and compare based on the two cols for Accepted / Not accepted, and this seems to be OK, however, I cannot seem to make it work for the default values. I'm using the following code:
strCondString = "=$I$" & CStr(lPos) & "=" & dtDefault With oWS.Cells(lPos, "I").FormatConditions.Add _ (Type:=xlExpression, Formula1:=strCondString) With .Font .Color = RGB(0, 0, 255) .Bold = True End With End Withwhere lPos is a row reference, and dtDefault is the date. strCondString will evaluate to something like"=$I$2=12/01/2004"The code seems to get through the compiler, in that it doesn't throw any errors when steppping over, but also doesn't colour the text at all - it stays black.....anyone? To add confusion (and not looked at this bit yet), how will it all stack up when I throw in the colouring for Accepted - all default dates will be "accepted", so is there any precedence in the FormatConditions that anyone knows about? "Now I guess I'll sit back and watch people misinterpret what I just said......" Christian Graus At The Soapbox -
Hi guys (again!) Some of you may have been folowing my escapades with the Excel Object model, and many thanks to those that have offered assistance! So today's problem is as follows.... Have a column containing dates (Action dates). Each value in the column can be in either one of three states based on the values in two other date cols( Start date and End date). The values can either be accepted - i.e. between the two other dates, and therefore green, Not accepted, i.e. outside the dates and therefore Red, or Default - a value read from a SProc in an SQLServer db, and hence Blue. Now using conditional formatting, can use xlCellValue type, and compare based on the two cols for Accepted / Not accepted, and this seems to be OK, however, I cannot seem to make it work for the default values. I'm using the following code:
strCondString = "=$I$" & CStr(lPos) & "=" & dtDefault With oWS.Cells(lPos, "I").FormatConditions.Add _ (Type:=xlExpression, Formula1:=strCondString) With .Font .Color = RGB(0, 0, 255) .Bold = True End With End Withwhere lPos is a row reference, and dtDefault is the date. strCondString will evaluate to something like"=$I$2=12/01/2004"The code seems to get through the compiler, in that it doesn't throw any errors when steppping over, but also doesn't colour the text at all - it stays black.....anyone? To add confusion (and not looked at this bit yet), how will it all stack up when I throw in the colouring for Accepted - all default dates will be "accepted", so is there any precedence in the FormatConditions that anyone knows about? "Now I guess I'll sit back and watch people misinterpret what I just said......" Christian Graus At The SoapboxInstead of relying on Excel's FormatConditions to perform conditional formatting, couldn't you rely on conditional logic instead, like so?:
Public Sub HighlightColor()
Dim i As Integer
For i = 1 To Range("MyRange").Rows.Count
If Not (Cells(i, 8).Value <= Cells(i, 9).Value And Cells(i, 9).Value <= Cells(i, 10).Value) Then
Cells(i, 9).Font.ColorIndex = 3 ' Red
End If
Next
End SubOr should the condition be in place for when someone interacts with the worksheet?
-
Instead of relying on Excel's FormatConditions to perform conditional formatting, couldn't you rely on conditional logic instead, like so?:
Public Sub HighlightColor()
Dim i As Integer
For i = 1 To Range("MyRange").Rows.Count
If Not (Cells(i, 8).Value <= Cells(i, 9).Value And Cells(i, 9).Value <= Cells(i, 10).Value) Then
Cells(i, 9).Font.ColorIndex = 3 ' Red
End If
Next
End SubOr should the condition be in place for when someone interacts with the worksheet?
John Kuhn wrote: Or should the condition be in place for when someone interacts with the worksheet? Unfortunately, you've hit the nail on the head! Am developing a utility to generate the spreadsheets based on different date ranges, which will then be used by others to enter data - hence the need to have all the formatting stuff in place before the user gets the sheets - it's not just a VBA project, but a full VB6 executable. Have managed to get around it though, by putting the default dates into a hidden column, and then using xlCellValue rather than xlExpression. Many thanks for the help though - hope I can return the favour some day! "Now I guess I'll sit back and watch people misinterpret what I just said......" Christian Graus At The Soapbox
-
John Kuhn wrote: Or should the condition be in place for when someone interacts with the worksheet? Unfortunately, you've hit the nail on the head! Am developing a utility to generate the spreadsheets based on different date ranges, which will then be used by others to enter data - hence the need to have all the formatting stuff in place before the user gets the sheets - it's not just a VBA project, but a full VB6 executable. Have managed to get around it though, by putting the default dates into a hidden column, and then using xlCellValue rather than xlExpression. Many thanks for the help though - hope I can return the favour some day! "Now I guess I'll sit back and watch people misinterpret what I just said......" Christian Graus At The Soapbox