excel training course - conditional formatting based val

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel training course - Conditional formatting based on the values in a different range

excel training course - Conditional formatting based on the values in a different range

resolvedResolved · Low Priority · Version Standard

Yuri has attended:
Access Advanced course
Excel Advanced course

Conditional formatting based on the values in a different range

Hi all

Is it possible to use conditional formatting on (e.g.) one column, based on the values in a different column? How would one go about this?

For example, I wish to highlight a staff member's name (column 1) based on their date of entry (column 2).

Thanks in advance for any assistance.

Yuri

RE: Conditional formatting based on the values in a different ra

Hi Yuri. It certainly is possible to do this. Format the cells in column 2 so that the date appears as a number (Format - Cells - Number Tab - Number option) and make a note of the number value you want: for example 09/06/1973 resolves to 26824

Then select the cell column 1 you want to format conditionally. Select Format - Conditional Formatting and change "Cell Value Is" to "Formula is" and enter the formula =$B$1>26824. Select what formatting options you want to appear conditionally in Column 1 and click OK.

If you now reformat column 2 back to the Date format the result will be when you enter a date greater than 09/06/73, the cell in column 1 will turn, say, red. Enter a date less than 09/06/73 and the cell will stay the same. In this way you can distinguish cell data on the basis of dates in another column.

RE: Conditional formatting based on the values in a different ra

Thank you Anthony, that definitely answered my query! So basically make a reference to the cell range you want to check in the 'formula is' condition.

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Hiding and unhiding columns using the keyboard

CTRL + 0 hides your columns and CTRL + SHIFT + ) unhides them although you would need to highlight the column letters either side as per normal

View all Excel hints and tips


Server loaded in 0.09 secs.