Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 10-10-2018, 02:50 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 123
Marcia is on a distinguished road
Default 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.
Attached Files
File Type: xlsx Form 1.xlsx (31.9 KB, 7 views)
Reply With Quote
  #2  
Old 10-11-2018, 04:08 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 328
ArviLaanemets is on a distinguished road
Default

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.
Attached Files
File Type: xlsx Form 1.xlsx (31.5 KB, 6 views)
Reply With Quote
  #3  
Old 10-11-2018, 01:34 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 123
Marcia is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
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.
Thank you, is there no way to shade half of the cell area in conditional formatting?
Reply With Quote
  #4  
Old 10-11-2018, 01:46 PM
p45cal p45cal is offline Windows 10 Office 2016
Competent Performer
 
Join Date: Apr 2014
Posts: 205
p45cal will become famous soon enough
Default

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.
Attached Files
File Type: xlsm msofficeforums40597Form 1.xlsm (43.9 KB, 6 views)
Reply With Quote
  #5  
Old 10-11-2018, 08:26 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 123
Marcia is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 10-12-2018, 04:20 AM
p45cal p45cal is offline Windows 10 Office 2016
Competent Performer
 
Join Date: Apr 2014
Posts: 205
p45cal will become famous soon enough
Default

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).
Reply With Quote
  #7  
Old 10-12-2018, 07:38 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 123
Marcia is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
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).

Fantastic..........
Reply With Quote
  #8  
Old 10-15-2018, 08:50 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 123
Marcia is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
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).

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.
Attached Files
File Type: xlsm School Form 2A.xlsm (38.2 KB, 20 views)
Reply With Quote
  #9  
Old 10-16-2018, 02:02 AM
p45cal p45cal is offline Windows 10 Office 2016
Competent Performer
 
Join Date: Apr 2014
Posts: 205
p45cal will become famous soon enough
Default

It's quite important to know how you want these triangles to appear, or be different from existing traingles…
Reply With Quote
  #10  
Old 10-16-2018, 07:04 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 123
Marcia is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
It's quite important to know how you want these triangles to appear, or be different from existing traingles…
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
Reply With Quote
  #11  
Old 10-16-2018, 09:57 AM
p45cal p45cal is offline Windows 10 Office 2016
Competent Performer
 
Join Date: Apr 2014
Posts: 205
p45cal will become famous soon enough
Default

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
Reply With Quote
  #12  
Old 10-16-2018, 04:06 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 123
Marcia is on a distinguished road
Default

Thank you P45Cal, from the bottom of my heart.
Reply With Quote
  #13  
Old 10-27-2018, 07:11 AM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 123
Marcia is on a distinguished road
Default

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
Reply With Quote
  #14  
Old 10-27-2018, 09:43 AM
p45cal p45cal is offline Windows 10 Office 2016
Competent Performer
 
Join Date: Apr 2014
Posts: 205
p45cal will become famous soon enough
Default

I need to kinow:
  1. All the codes which you want to interpret and what you want to appear when they're encountered.
  2. If there are going to be multiple codes in one cell then I need to know all the possible combinations of such code and what you want to see when those combinations occur.
Bear in mind that currently, the single leftmost character in a cell determines what's shownfor the morning; anything after that (2nd character onwards) is going to be assigned to the afternoon. If that's no longer going to be possible then a rethink is required (on my part). You could, for example, instruct your users that everything to the right of a / character (and/or a \ character) applies to the afternoon; and if there is no such character everything in the cell applies to the morning. Alternatively, you could divide each day into 2 cells, the left cell relating to the morning and the right cell the afternoon.
Reply With Quote
  #15  
Old 10-27-2018, 05:25 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Competent Performer
 
Join Date: May 2018
Posts: 123
Marcia is on a distinguished road
Default

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?
Attached Files
File Type: xlsm JUNE 2018 QUERYB.xlsm (116.4 KB, 4 views)

Last edited by Pecoflyer; 10-30-2018 at 04:26 AM. Reason: Removed quote
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 12:42 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft