Conditional Formatting

Crispyboggins

New Member
Joined
Jul 28, 2019
Messages
5
Morning,

I have a spreadsheet Training Matrix with multiple columns. In colum D is has the value of 1 if training has been completed or 0 if not completed. In colum E there is a option for the date of training. Column D is updated from another sheet so what i need to do is for the coresponding cell in column E to change colour if no date is entered so the Training Officer knows that a date needs to be entered. E.g if D5 was 0 E5 will do nothing but is D5 is 1 and E5 is blank E5 will turn RED

1587544919629.png
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,103
Office Version
  1. 2013
Platform
  1. Windows
Try like..
Book1
DE
4TrainedDate
5101/01/2020
61
71
80
9105/01/2020
101
110
12108/01/2020
Sheet7
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E5:E13Expression=AND($D5=1,$E5="")textNO


Hope that helps.
 

Crispyboggins

New Member
Joined
Jul 28, 2019
Messages
5
Hi Snakehips,

Thanks for this. Not sure if I am doing something wrong but its not doing anything... Have posted the Conditional Formatting Screens below. Anything you can think of?

1587546968168.png
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,103
Office Version
  1. 2013
Platform
  1. Windows
The fact that you expect the Training Officer to enter the dates in E leads me to believe that cells in E are normally empty.
No formulas in E, Is this the case?
Also, no danger you have any stray spaces incells?
 

Crispyboggins

New Member
Joined
Jul 28, 2019
Messages
5
Yeah cells in E are empty till date entered. Not sure if this makes a difference. Have added column C below as well. So Column C populated from a nother sheet within the workbook with a simple cell = cell formula. Column D has the formula =IF(C5>0,"1","0") which fills in column D. Does that cause any issues?

1587565716331.png
 

Watch MrExcel Video

Forum statistics

Threads
1,127,087
Messages
5,622,635
Members
415,915
Latest member
Eng Said Ebead

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
Top