Working with Colour

[max-power]

Hi all,

I've recently taken the CBT nugget traing and looking to really pimp my workbooks.

Firstly is what I want to do possible?

Goal - to return a Vlookup with a date (which I've managed to do), then if that date is within this month display the date with a red background, if it's next month yellow, anything after green.

Thanks a bunch.
Chris

[max-power]

Ohh and I have one other one sorry.

I want to vlookup on a seperate sheet and have it display the number of times it matched the lookup.

[nsv]

My Excel version is not in english so I'm, not sure if I use the right terms, but they are close.

Put the cursor in A1
Enter Format - Conditional Formatting, change from "Cell value" to "Formula value" and enter this formula

Please Login or Register  to view this content.

Set the condition to be red background
Copy the A1 and paste format to other cells where you want it.

I think you should try to do the rest yourself. Good luck.


NSV

[max-power]

Hey thanks heaps for pointing me in the right direction.

The formular is giving me an error though when I hit enter.

I could spend days on this so I thought I'd just ask, Also how would I tack the other colours on.. "now+1"?

[max-power]

Hey.. ok I found an alt way of doing it with.

Select all the dates which may change color. Let's assume they're in column C and you've selected the whole column. Use Format/Conditional Formatting.
Assuming C1 is the active cell:
Change "cell value is" to "formula is" and enter

Please Login or Register  to view this content.

click the Format button, patterns tab, select a red color
click OK
click the Add button
Change "cell value is" to "formula is" and enter

Please Login or Register  to view this content.

click the Format button, patterns tab, select a yellow color
click OK
click OK.


Another question tho.. I'm doing a vlookup to return a date, if there is no data in a cell how do I make it display another cell.

EG if nothing is in cell B1 return cell C1.

Also after the formular for returning the number of hits.

EG Vlookup A1 return number of matchs on 'test' wooksheet.