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
Attendance Sheet 1-12-2018
12345678910111213141516171819202122232425262728293031Total-PTotal-L
majidppppppLpppppppppppppppppppppppp301
sajidppppppppppppppppppppppppppppppp310
khalidppLLppppLppppppppppppppppppLppp274
ahmedppppppppppppLpppppppppppppppppL292
NasirpppppppLLpppLppppppLppppppppppL265

<colgroup><col><col span="9"><col span="22"><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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?

Attendance Sheet 1-12-2018
12345678910111213141516171819202122232425262728293031Total-PTotal-L
majidppppppppppppppppppppppppLLLpppp283
sajidpppppLpppLppppLpppppppppppppppp283
khalidppppppppppppppppLpppppppppppLLp283
ahmedpppppppppppppppppLppppppppppppp301
NasirpLpLpLppppppppppppppppppppppppp283

<tbody>
</tbody>

<colgroup><col><col span="31"><col><col></colgroup><tbody>
</tbody>
 
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???

AGAHAIAJ
Total-Black PTotal-LTotal Red(p) & LTotal Red (p)
<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> </tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
My VLookup is not working
Replies
6
Views
299
Anthony47
A
A
  • Solved
COUNT TWO OR MORE TEXT FROM RANGE WITH A SINGLE FORMULA
Replies
5
Views
276
Fluff
Fluff
F
  • Solved
how to automate attendance sheet
2
Replies
13
Views
961
awoohaw
awoohaw
D
  • Locked
  • Question
Formula to check data contingent on multiple constraints
Replies
1
Views
124
Peter_SSs
Peter_SSs
G
  • Solved
If statement stopped working from one cell to the next, with formatting all the same
Replies
7
Views
132
Guzzlr
G

Forum statistics

Threads
1,214,832
Messages
6,121,845
Members
449,051
Latest member
excelquestion515

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