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
Advanced Beginner
 
Join Date: May 2018
Posts: 37
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, 5 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: 299
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, 3 views)
Reply With Quote
  #3  
Old 10-11-2018, 01:34 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Advanced Beginner
 
Join Date: May 2018
Posts: 37
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: 170
p45cal is on a distinguished road
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, 3 views)
Reply With Quote
  #5  
Old 10-11-2018, 08:26 PM
Marcia Marcia is offline Windows 7 32bit Office 2007
Advanced Beginner
 
Join Date: May 2018
Posts: 37
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: 170
p45cal is on a distinguished road
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
Advanced Beginner
 
Join Date: May 2018
Posts: 37
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
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 03:08 PM.


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