Attendance Sheet problem

M

majidsiddique

Board Regular
Joined
Oct 22, 2018
Messages
164
hi all,
i am making a attendance sheet month wise.
AG=COUNTIF($B3:$AF3,"p")
AH=COUNTIF($B3:$AF3,"L")
i want, if L more then 3 days Leave then p automatic replace with L fro given range.
cell color must be change in RED color only for this condition apply.
Khalid and nasir is example of this.
Nasir p replace 2
kahlid p replace 1

thanks
[TABLE="width: 747"]
<colgroup><col><col span="9"><col span="22"><col><col></colgroup><tbody>[TR]
[TD="colspan: 34"]Attendance Sheet 1-12-2018[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]Total-P[/TD]
[TD]Total-L[/TD]
[/TR]
[TR]
[TD]majid[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]30[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]sajid[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]31[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]khalid[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]27[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]ahmed[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]29[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Nasir[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]26[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Try this:-
Place in Worksheet module:-
Code runs when you change any "p" to "L"
Code: 
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
Application.EnableEvents = False
[COLOR="Navy"]If[/COLOR] Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
  [COLOR="Navy"]If[/COLOR] Application.CountIf(Cells(Target.Row, 2).Resize(, 31), "L") > 3 [COLOR="Navy"]Then[/COLOR]
      [COLOR="Navy"]For[/COLOR] Ac = 32 To 2 [COLOR="Navy"]Step[/COLOR] -1
          [COLOR="Navy"]If[/COLOR] Cells(Target.Row, Ac).Value = "L" [COLOR="Navy"]Then[/COLOR]
            Cells(Target.Row, Ac).Font.Color = vbRed
            Cells(Target.Row, Ac).Value = "p"
          [COLOR="Navy"]End[/COLOR] If
          [COLOR="Navy"]If[/COLOR] Application.CountIf(Cells(Target.Row, 2).Resize(, 31), "L") < 4 [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] For
     [COLOR="Navy"]Next[/COLOR] Ac
 [COLOR="Navy"]Else[/COLOR]
    Cells(Target.Row, 2).Resize(, 31).Font.Color = vbBlack
 [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks MickG
its not working, i copy it and paste Macro window and RUN, then ask me again macro name.
can it should be working automatic when do any entry in a sheet?
Khalid and Nasir case would automatic "P" convert to "L" and background color RED. both are higher than 3L. replace "P" to "L" from any given range of both start to end.
 
Upvote 0
Thanks MickG:
well done sir,
please support me one more thing in it. background color RED and font color Blank or as you wish when increase 3 L and how countif formula deduct RED background P in a sum. if i want to sumup RED P in next column.
majid Total P = 24 and RED P and L are 7 ( Leave ).
in the below bold replace with L i put it so total leave of majid is 7. how to sum RED P and L?
is it possible to calculate with formula?

[TABLE="width: 1312"]
<colgroup><col><col span="31"><col><col></colgroup><tbody>[TR]
[TD]Attendance Sheet 1-12-2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[TD]27[/TD]
[TD]28[/TD]
[TD]29[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]Total-P[/TD]
[TD]Total-L[/TD]
[/TR]
[TR]
[TD]majid[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]28[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]sajid[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]28[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]khalid[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]28[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]ahmed[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]30[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Nasir[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]L[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]p[/TD]
[TD]28[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="align: left"]

<tbody>
</tbody>
[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
If I understand you correctly, you want the total count of the Red(p)'s and the "L" values.
I have altered the code so the result will show in column "AI".
Code: 
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
Application.EnableEvents = False
[COLOR="Navy"]If[/COLOR] Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
  [COLOR="Navy"]If[/COLOR] Application.CountIf(Cells(Target.Row, 2).Resize(, 31), "L") > 3 [COLOR="Navy"]Then[/COLOR]
      [COLOR="Navy"]For[/COLOR] Ac = 32 To 2 [COLOR="Navy"]Step[/COLOR] -1
          [COLOR="Navy"]If[/COLOR] Cells(Target.Row, Ac).Value = "L" [COLOR="Navy"]Then[/COLOR]
            Cells(Target.Row, Ac).Font.Color = vbRed
            Cells(Target.Row, Ac).Value = "p"
          [COLOR="Navy"]End[/COLOR] If
          [COLOR="Navy"]If[/COLOR] Application.CountIf(Cells(Target.Row, 2).Resize(, 31), "L") < 4 [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] For
     [COLOR="Navy"]Next[/COLOR] Ac
     
 [COLOR="Navy"]Else[/COLOR]
    Cells(Target.Row, 2).Resize(, 31).Font.Color = vbBlack
 [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]For[/COLOR] Ac = 2 To 32
        [COLOR="Navy"]If[/COLOR] Cells(Target.Row, Ac).Value = "L" Or Cells(Target.Row, Ac).Font.Color = vbRed [COLOR="Navy"]Then[/COLOR]
            c = c + 1
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] If
Cells(Target.Row, "AI").Value = c
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG Boss,
same error i do copy and paste it again macro name window appear.
kindly i dont have enough knowledge regarding macro coding. this is not run. coding should be always working every time when file open.
this is not possible by excel formula.
 
Upvote 0
Thanks a Lot MickG
you put great effort to solve this.
kindly in this add one thing.
Countif of "P" is not calculating correctly.
kindly update countif to count only "p" minus "L" and RED "p" to show actual presents. only update RED "p" counting to minus total "P"
DEC = 31
RED = 2
L = 3
Result of P = 26
countif count given range 28 then minus both result is 23.
also tell me i have to work always on this sheet, is there any possibility to move this coding to another sheet?
Thanks.
 
Upvote 0
I'm not sure what your asking.
From the Range below , send me back what You would like to see alter as required???

[TABLE="width: 308"]
<colgroup><col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 3925;"> <col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2503;"> <col width="115" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4096;" span="2"> <tbody>[TR]
[TD="width: 110, bgcolor: white"]AG[/TD]
[TD="width: 70, bgcolor: white"]AH[/TD]
[TD="width: 115, bgcolor: white"]AI[/TD]
[TD="width: 115, bgcolor: white"]AJ[/TD]
[/TR]
[TR]
[TD="bgcolor: white"]Total-Black P[/TD]
[TD="bgcolor: white"]Total-L[/TD]
[TD="bgcolor: transparent"]Total Red(p) & L[/TD]
[TD="bgcolor: transparent"]Total Red (p)[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Formulas with Inititals with Last Name
Replies
3
Views
98
Fluff
Fluff
Cliffork
  • Solved
Print to PDF Macro, or possibly more
2 3
Replies
21
Views
314
mumps
mumps
E
  • Question Question
My VLookup is not working
Replies
6
Views
472
Anthony47
A
A
  • Solved
COUNT TWO OR MORE TEXT FROM RANGE WITH A SINGLE FORMULA
Replies
5
Views
407
Fluff
Fluff
V
  • Question Question
Sort problem
2
Replies
10
Views
369
Alex Blakenburg
A

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top