Conditional Formatting & Reading Fill Value

[Bopo2]

Hi, Okay here's my problem,

I use conditional formatting to highlight weekends of a calendar via filling them with a gray color, and in VBA I perform some opperations involving looping through each cell and checking the color.

After an hour of troubleshooting, I've come to realise that due to the conditional formatting, the fill color remains the same in VBA's eyes, therefore it's not reading the cell fill colors. If you go to the fill option for excel, it says None :(

Here's a similar calendar to the one I'm using which uses conditional formating

http://www.vbaexpress.com/forum/showpost.php?p=197979&postcount=3

If anyone can figure out how to actually read those colors some how I'll be amazed, here's a bit of messy code to help

Dim i As integer

i = Worksheets("Calendar").Range("S10").Interior.ColorIndex

'it always returns -4142 despite the color :(


Help appreciated.

P.S Even if you can just change the conditional format to insert a character into cells that are weekends I'd be grateful, just anything to distinguish the weekend cells from others that vba can see :(

[Bopo2]

Dam I thought I'd found a solution, although still returns everything as 255 which is the RGB color for white I believe


dim i as integer
i=Worksheets("Calendar").Range("R18").FormatConditions(1).Interior.Color

[mdmackillop]

For CF help try XLD's site (http://www.xldynamic.com/source/xld.CF.html)

With regard to your problem, CF does not set the cells Interior property (though it appears the same). To determine the colour, you need to test the cell with the same logic that sets the colour, and use the same colour value in your code.

[Bopo2]

Thanks for the suggestion and link, I'm trying to figure out a solution to this, the only thing I've come up with is some sort of if statement which evaluates against the weekend formula and loops through each cell to see if the condition for that cell matches the weekend formula. However I haven't put it into practise as I know someone must be able to come up with a more elegant solution

[p45cal]

So you want to determine if a date is a weekend as you loop through the range.
If you supply numeric values for AYear, AMonth and ADay then this vba will be True if it's a weekend:
Weekday(DateSerial(AYear, AMonth, ADay), vbMonday) > 5

In XLD's calendar that you cite, these are easy to obtain; the year is in A1, the month number is the cell's row number -1 and the day is the cell's column number -1.

There is a little problem of some dates going beyond the end of the month but XLD solves this in his conditional formatting. That has been addressed in the code below, you just have to supply your own sources for those three variables.

Sub blah()
AYear = Range("A1").Value
For Each cll In Range("$B$2:$AF$13")
AMonth = cll.Row - 1
ADay = cll.Column - 1
If Application.WorksheetFunction.EoMonth(DateSerial(AYear, AMonth, 1), 0) >= DateSerial(AYear, AMonth, ADay) _
And Weekday(DateSerial(AYear, AMonth, ADay), vbMonday) > 5 Then
cll.Value = "x" 'or whatever processing you want to do.
Else
cll.Value = Empty 'or whatever processing you want to do.
End If
Next cll
End Sub

If this is too unwieldy for you, you could create an IsWeekend function:
Function IsWeekend(AYear, AMonth, ADay)
IsWeekend = Application.WorksheetFunction.EoMonth(DateSerial(AYear, AMonth, 1), 0) >= _
DateSerial(AYear, AMonth, ADay) And Weekday(DateSerial(AYear, AMonth, ADay), vbMonday) > 5
End Function
and call it thus:
Sub blah2()
AYear = Range("A1").Value
For Each cll In Range("$B$2:$AF$13")
AMonth = cll.Row - 1
ADay = cll.Column - 1
If IsWeekend(AYear, AMonth, ADay) Then
cll.Value = "x" 'or whatever processing you want to do.
Else
cll.Value = Empty 'or whatever processing you want to do.
End If
Next cll
End SubAnd to shorten the code still further (though it will be slower) you could use:
Sub blah3()
For Each cll In Range("$B$2:$AF$13")
If IsWeekend(Range("A1").Value, cll.Row - 1, cll.Column - 1) Then cll.Value = "x" Else cll.Value = Empty
Next cll
End Sub
Note that I haven't qualified the worksheet/workbook etc. when referring to ranges so either make sure the apropriate sheet is active, or qualify all the references.

[p45cal]

Bopo2 can't be bothered to respond?
Perhaps on holiday.