Writing multiple conditional formats to a training matrix for cell coloring

[theYaniac]

I have created a simple employee training records data base. The range of cells are column L through Column Z. In the columns are expiration dates for the training in the column, confined space entry, fork lift driver, etc. I am attempting to get 3 different cell colors based on the current date versus the training expiration. If the training is current the cell would be highlighted green, if the training expires within 45 days cell would be yellow, if the training is expired the cell would be red. I have been able to get this with regular conditional formatting, however, it makes the spreadsheet extremely slow. I would like to put the conditional formatting in VBA code. I am very new to VBA, any help or suggestions would be greatly appreciated.


I could attach an example file if necessary.

 

[NARAYANK991]

Hi ,

Please attach a workbook with enough data in it.

Narayan

 

[GraH - Guido]

Hi theYaniac,
I understand the workbook is not "slow" without the CF in place, correct?
Some other questions...
Are you using data tables or plane ranges? -> data tables might be better
Do you use relative references in the CF formulas? -> they are great, but if done incorrectly, they quickly become hell (from experience!).
Do you reference the full columns? -> almost every time a bad idea. The more cells are referenced the slower Excel gets.
You might want to add a calculated column (formula) in the data that checks the current date* with the training date. And set your CF on this new column.

* TODAY () is a volatile function, causing Excel to recalculate each time you do something in the workbook: add date, set a filter, ...

 

[theYaniac]

I have attached an example copy of the work book I am currently working on. The types of training certifications the employees will receive is going to continue to expand as well as the number of employees that will be entered into the workbook. This is the main reason I would like to enter the formatting in the code to allow for the expansion. The formatting wouold need to be applied form column M through column U. Again, I am fairly new to VBA so if there appears to be something that could be written cleaner or more efficient I would love to be educated.

 

[GraH - Guido]

I did not resort to VBA for the CF. I do not know much about VBA.
I noticed that the dates in the data range was actually entered as text. You cannot compare text to dates.
Once they were converted to dates the CF worked. Basically I created 4 formulas. And 1 new named range for your amber date.
I also converted the range to a table to allow the CF range to grow with the data. But depending on how new data arrives, this may slow down the Excel: copy inside a table => slow; copy adjacent to the table => fast.

 

[theYaniac]

Thank you, it looks great. The new data arrives via a user form. The employee data is also edited via a user form. The edit user form has a drop box to search for the employee and then will update with any information that is changed. Thank again

 

[vletm]

theYaniac
Ooops - am I too late?
I offer some VBA ... ex for those 'date colors'.
a) Automatic, after open that file
b) Manually, if select 'Your Today's Date .. Cell [L2]
c) Automatic, after You update Your Form ...
d) If You would like to modify single date from sheet then that option can add too (update automatic color or now, You can always do it manually b) )
Note 1. You update those 'dates' as text from Form ... modified
Note 2. Some of Your dates were as mm/dd/yyyy and some dd/mm/yyyy
That will always makes some challenges ... now ... dd-mmm-yyyy
(maybe some dates are changed,
only You can know was 6/10/2018 - June or October
Anyway [L2] date is today ... and January!)
ps ... I did some other minor modifications too

 

[theYaniac]

@vletm, thank you. After looking at the changes you have put in for the code I have a question.

For x = 13 To 21
            .Cells(TargetRow, x).Interior.ColorIndex = xlNone
            DV = .Cells(TargetRow, x)
            If DV <> "NO" Then
                .Cells(TargetRow, x) = DateValue(DV)
                .Cells(TargetRow, x).NumberFormat = "dd-mmm-yyyy"
                If IsDate(.Cells(TargetRow, x)) Then
                    Ddate = .Cells(TargetRow, x) - to_day
                    bg = 20
                    If Ddate < 45 Then bg = 6
                    If Ddate <= 0 Then bg = 18
                    .Cells(TargetRow, x).Interior.ColorIndex = bg
                End If
            End If
        Next x
    End With
[code]

Where x = 13 to 21, if I add training columns will I need to change the statement here? For example x = 13 to 27 if 6 new training certs are entered to the table.

Thank you again

 

[vletm]

theYaniac ... yes!
but same feature have to take care in few more places too...
ex updating new row to sheet ...
Check this version ...
ps ... maybe better that You would compare all codes with Your original file

 

[theYaniac]

theYaniac ... yes!
but same feature have to take care in few more places too...
ex updating new row to sheet ...
Check this version ...
ps ... maybe better that You would compare all codes with Your original file

Given the new version, the only additional code I would need to add would be to call out the specific new target rows:

.cells(TargetRow, 22) = New Cert
.cells(TargetRow, 23) = New Cert etc, etc
[code]

 

[vletm]

.. yes, like above rows
after those there is that
from 'txt to 'date' section
including cell color
and there is 'shorter' borders too
... only You know when do You would like to do that

 

[theYaniac]

.. yes, like above rows
after those there is that
from 'txt to 'date' section
including cell color
and there is 'shorter' borders too
... only You know when do You would like to do that

With the changes you made, something has happened with my edit userform. When you edit an employee record, it no longer updates the employee in the row the record is located in. It updates the employee record and places the updated information in the first row on the data sheet.

 

[vletm]

...sorry ... You'll get a new file soon ..

 

[theYaniac]

...sorry ... You'll get a new file soon ..

no problem, I am just having trouble figuring out where the issue is.

 

[vletm]

theYaniac
no 'P...'-words ... use 'challenge'.
Sorry, I didn't test my 'minor' all affects or effects ...
Now, I tested more and this would work more like before ...
(There were some feature which I won't normally use ... sorry)
Even one minor modification can cause ... interesting side effects.

 

[theYaniac]

This file still does not allow for edit. When you update employee information, the validation sub routine runs to check for existing employee with the name being edited. The sub routines returns the message of name already exists.

 

[vletm]

theYaniac
... still same same sorry
I missed that part to check ... and modify.
> Same time ... thinking ...
if You will add those columns to add more dates
then it would be possible to use 'sheet-as-a-form'.
It would be quicker to edit code and so on ...
Then no need to handle cells one-by-one as now!
>> I didn't start to do that 'idea' <<