check chip's site
http://www.cpearson.com/excel/CFColors.htm
--
Gary
"Akash" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi Gary,
>
> I use conditional formatting in my sheet. Let me explain you the
> entire sheet:
>
> E F G
> Time In | Time Out | Time In
> 9:30AM | 8:25 PM | 9:31 AM
>
> Members working late after 20:30hrs will be given the permission to
> come to work the next day by 10:30hrs.
>
> So in Column F i have placed only one condition that if the employee
> works after 20:30hrs make the font bold otherwise regular fonts.
>
> Our company policy is:
>
> If worked till 20:29hrs then the employee is allowed to resume his
> work by 9:30hrs next day, if after 9:30hrs it should be Red in color.
>
> if worked till 20:30hrs then the employee is allowed to resume his
> work by 10:30hrs next day. if after 10:30hrs it should be red in
> color.
>
> So i used this conditions in my sheet.
>
> n column G, cell G5, conditional formatting for Condition 1:
> Formula is: =AND(F5<TIME(20,29,0),G5>TIME(9,30,0))
>
> Condition 2 in G5 is:
> Formula is: =AND(F5>=TIME(20,29,0),G5>TIME(10,30,0))
>
> For column F , cell F5 conditional formatting is:
> Cell Value is | Greater Than | 0.853472222222222 .
>
> Its working fine up to this
>
> Now my question was how to count no of cells with Bolds.& Red Strike
> Through Effect
> Column A is to count no of cells with Bold Fonts
> Column B is to count no of cells with Red Strike Through Effect
>
> I have data in Column E5 : BH5
>
> So cell A5 would count no of cells with bold fonts in cell E5 : BH5
> And cell B5 would count no of cells with Red Strike Through Effect in
> cell E5 : BH5
>
> U had given me the resolution
>
> Sub stantial()
> Dim ws As Worksheet
> Set ws = Worksheets("Time Token Sheet")
> Dim myRange As Range, c As Range
> Dim i As Long
> ws.Range("a5:B30").Clear
> Set myRange = ws.Range("e5:BH30") '< Alter to suit
> For Each c In myRange
> If c.Font.Strikethrough = True And c.Font.ColorIndex = 3 Then
> ws.Range("B5:B30").Value = ws.Range("B5").Value + 1
> ElseIf c.Font.Bold = True Then
> ws.Range("A5:A30").Value = ws.Range("A5").Value + 1
> End If
> Next
> End Sub
>
> It is working a bit fine but as i had used Conditional Formatting so
> we don't have physically Bold Fonts or Red Strike Through Effect
> fonts.
> Its the conditional formatting doing that manually.
>
> What could be done to solve this query.
>
> Awaiting for your mail.
>
> Thanks
>
> Akash Maheshwari
>
Similar Threads
