|
|
Thread Tools | Display Modes |
|
#1
|
||||
|
||||
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
In a learner's daily attendance sheet, tardiness is marked through triangle shades if a learner is late in the morning or afternoon classes, but if he/she is late in both morning and afternoon the whole box is shaded. A square cell representing the day of classes is divided by a diagonal line creating two triangles. What's the formula to automatically effect the symbols for tardy? If a type "t" left align, the upper triangle (morning) should be shaded, if I type "t" right align, the lower triangle should be shaded, if I type "tt", the whole box should have a full shade. What I am doing is circuitous (after typing "t", I then copy paste the triangle), I know there is a neater and faster way of doing it but I don't know how. |
#2
|
|||
|
|||
Conditional Formatting is for changing the outlook of cells or their contents.
You have Shapes (i.e. certain Objects) placed over cells - from 0 up to 2 different Objects per cell area (NB! Not in cell!). An example how to do something using Conditional Formatting is in attached workbook. |
#3
|
||||
|
||||
Quote:
|
#4
|
||||
|
||||
I played with conditional formatting and couldn't make it work, so…
In the attached, a button in the vicinity of cell AL10. Triangles are removed then added. The first letter of a cell represents the morning. Any letters after that represent the afternoon. If you want to signify only tardy in the afternoon, then start the cell with, say, a space (or more than one space(or any other character)),then enter a t character. The same for absent, but use an x. For demonstration, an oddity in cell J25. How would you or I interpret it? Currently all the triangles are at 50% transparency so that you can check the triangles versus the cell content behind being visible. To get rid of the transparency, disable/delete the code line myshape.Fill.Transparency = 0.5. There's a couple more lines of code to delete later,left there for demonstration purposes, they are: Application.ScreenUpdating = True'delete this line later. MsgBox "Triangles removed" 'delete this line later. |
#5
|
||||
|
||||
This is amazing. The teachers will be over the moon when I show them this macro operated symbols for their late and/or absent learners. Re J25, I think no pupil will be absent and tardy on the same class. Again, thank you.
|
#6
|
||||
|
||||
Currently there's a line around each triangle - you may find it more pleasing not to have that line. If so, you can add the code line:
myshape.Line.Visible = msoFalse right next to the myshape.Fill.Transparency = 0.5 line (whether that line is disabled/deleted or not). |
#7
|
||||
|
||||
Quote:
Fantastic.......... |
#8
|
||||
|
||||
Quote:
Hi. Back to this thread. I tried adding another condition, Cutting Classes. I inserted codes for Cutting Classes by following the codes for Absent and Tardy. I got stumped on the codes for the triangles. Please help again. |
#9
|
||||
|
||||
It's quite important to know how you want these triangles to appear, or be different from existing traingles…
|
#10
|
||||
|
||||
Yeah sorry. Similar with Tardy and Absent, Cutting classes in the morning is characterized by a shaded triangle (upper left of a rectangle) while cutting classes in the afternoon is shaded in the lower right. The colors of triangles to differentiate one case from the other are - Absent - red, Tardy - blue and Cutting Classes - yellow
|
#11
|
||||
|
||||
Code:
Sub blah() Set RngToCheck = Range("D13:AB63") 'clear triangles: For Each shp In ActiveSheet.Shapes If Not Intersect(shp.TopLeftCell, RngToCheck) Is Nothing Then shp.Delete Next shp 'new triangles: For Each cll In RngToCheck.Cells Select Case UCase(Left(cll.Value, 1)) Case "X" AddTriangle cll, "AM", "ABSENT" Case "T" AddTriangle cll, "AM", "TARDY" Case "C" AddTriangle cll, "AM", "CUTTING CLASSES" End Select x = Application.Search("x", cll.Value, 2) t = Application.Search("t", cll.Value, 2) c = Application.Search("c", cll.Value, 2) If Not IsError(x) Then AddTriangle cll, "PM", "ABSENT" End If If Not IsError(t) Then AddTriangle cll, "PM", "TARDY" End If If Not IsError(c) Then AddTriangle cll, "PM", "CUTTING CLASSES" End If Next cll End Sub Sub AddTriangle(myCell, ampm, TardyAbsent) Set myshape = ActiveSheet.Shapes.AddShape(msoShapeRectangle, myCell.Left, myCell.Top, myCell.Width, myCell.Height) Set yyy = myshape.Nodes If UCase(ampm) = "AM" Then yyy.Delete (3) 'delete bottom right node Else yyy.Delete (5) yyy.Delete (1) End If Select Case UCase(TardyAbsent) Case "TARDY" myshape.Fill.ForeColor.RGB = 13998939 'blue Case "ABSENT" myshape.Fill.ForeColor.RGB = 255 'red Case "CUTTING CLASSES" myshape.Fill.ForeColor.RGB = 65535 'yellow End Select myshape.Line.Visible = msoFalse End Sub |
#12
|
||||
|
||||
Thank you P45Cal, from the bottom of my heart.
|
#13
|
||||
|
||||
Hi, a problem came out from the code for Tardy. In the range D13:AB64, there are other codes like "T/O" and "T/I". When I run the macro, the "T/O" and "T/I" were also shaded. I changed the Tardy to Late which was fine, but again there was code "LE" and the macro shaded the "LE". Next I replaced Late to Delayed but it became worse because all the cell areas (lower right) of D13:AB64 were shaded. Please help again.
Last edited by Pecoflyer; 10-30-2018 at 04:26 AM. Reason: Removed quote |
#14
|
||||
|
||||
I need to kinow:
|
#15
|
||||
|
||||
Okay. We can not divide the day into 2 cells because according to my teacher friends whom we are helping to make their life easier every EOM, the day should be divided by a broken diagonal line. These are the codes in E16:AI28
am absent ( "X","XTI","XLE" left align) no shade, only the X letter is visible pm absent ("X","XTI",XLE" right align) no shade, only the X letter is visible whole day absent ("X"four spaces"X", "XTIX","XLEX") no shade, only the X letters are visible. 4 spaces so that the "X" will appear on the right and left of the cell area Tardy ("L","LTI","LLE" left align) upper left triangle shaded, font size 4 so that the triangle will cover the letters Whole day tardy (""LL","LLTI","LLLE") whole cell area shaded, font size 4 Cutting Classes ("C","CTI","CLE" right align) lower right triangle shaded, font size 4 Transfer Out ("T/O") white color font Transfer In ("T/I") white color font Drop Out ("DRP") white color font Late Enrollee ("LE") white color font I amended the last macro that you made, see attached sheet, but I came upon the "LE" problem. Another one is when a day is already marked by a formula as "LE" or "T/I", and should also be marked manually as absent, late or cutting classes, thus the multiple or combined codes XTI, XLE, LTI, LLE, CTI, CLE, XTIX, XLEX, LLTI, LLLE. Please also take notice of the formulas in Rows 21 and 26, Columns AJ and AI. Columns AJ and AI either count the absences or tardiness while Rows 21 and 26 count the learners in attendance daily. Cells C4 and C26 are the number of enrolees at the beginning of the school year. I hope I am making sense. There will be a conditional formatting that will turn all the typed remarks except the remarks for absences ("X", "X X")into white font color, before printing the form, something that I learned how to do. The printed form will show only the Xes and the shades for late and cutting classes. BTW, I asked for shaded absent, tardy and cutting classes, both am and pm earlier for my future references. Is there a better way to shorten the very long formula in E21 to AI21? Last edited by Pecoflyer; 10-30-2018 at 04:26 AM. Reason: Removed quote |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
New learner need help - convert tables in word format to ppt slides. | sas | Word VBA | 5 | 09-21-2017 08:33 PM |
Good morning/afternoon/evening based on time | dwirony | Word VBA | 1 | 01-03-2017 01:01 PM |
Need conditional formatting formula | namedujour | Excel | 1 | 05-27-2016 08:58 AM |
Conditional Formatting Formula | teza2k06 | Excel | 1 | 08-22-2014 02:49 AM |
Conditional Formula | UICOMP | Excel | 1 | 05-18-2012 12:59 PM |