Advanced Conditional Formatting

**dimitrz** · 09-11-2008, 09:19 AM

Hi All,

I am perplexed with a Conditional Formatting requirement

I have a tracker and one of the columns need to be updated religiously – and if it doesn’t happen then I want that cell to be highlighted in RED colour (ie if a cell in that column is blank then it would be highlighted in red colour)

Now if I place a normal conditional formatting then all the blank cells in that column would be highlighted even in the rows which have not yet been filled – this makes the entire exercise effective since instead of say two or three highlighted cell which stands out there is a entire series of cells in the column marked in the same colour ( view example 2)

Is there a way by which if the Cell in the respective column (lets say Department) is Blank then the cell is highlighted but only if that particular Row is active (i.e. lets say there is some data in row like the Name Column is active with any of any Technician )

If the Name column is Blank then whether the Department column remains blank or not is immaterial so no highlighting should happen then.

Name - Date –Time- Issue – Department

Sample attached (sample has a macro for date)

Thanks

**dimitrz** · 09-11-2008, 09:23 AM

Cross posted at

http://www.mrexcel.com/forum/showthr...31#post1682031

**NBVC** · 09-11-2008, 09:35 AM

You mean....

using Formula Is option in CF dialogue...

=AND($A2<>"",$F2="")

**rwgrietveld** · 09-11-2008, 09:42 AM

Use
=AND(IF(INDEX(A:A;ROW();1)<>"";1;0);IF(INDEX(F:F;ROW();1)<>"";1;0))
Now it only turns red if there is no value in column A either

**rwgrietveld** · 09-11-2008, 09:45 AM

NBVC,

Much better

**dimitrz** · 09-12-2008, 03:50 AM

Thanks NBVC & rwgrietveld

rwgrietveld there seems to be a slight error in the formula because excel keeps prompting me here at Second "A" of an error " INDEX(A:A;ROW "

Please Login or Register  to view this content.

Ps. At my cross post I have recived an answer which seems to be working

this is the code that I got and for idiots like me - You have to put this formula inside the Conditional Formatting Forumla box

Please Login or Register  to view this content.

Thanks all for the time taken

**dimitrz** · 09-12-2008, 08:07 AM

Hi a Slight problem with this code

Please Login or Register  to view this content.

Well in the sample tracker sheet attached

When the Name is selcted from the drop down menu the Date and Time are auto insterted due to a macro (they are further refrenced to cell D & E to protect B & C which is hidden) The Priority Column is Placed at "N" and the Name Column is at "A"

So all that I want is the formula to check if there is any data in Column A " Name field" and if there is data there and no data in column "N" Priority then the Priority cell should be highlighted

The formulas should not take B,C,D&E into consideration since there is always some data there.

I did try few changes here and there in the formula but nothing worked.

Can you please check this and if possible advise me how the formula parameter works so that I could understand how the formula worksl.

In Brief

Cloumn position

A----------------------------BCDE--------------------N
Name-----Auto Date (always has some data)---Priority

**NBVC** · 09-12-2008, 08:10 AM

So my previous suggestion didn't work?

i.e.

=AND($A2<>"",$N2="")

Placed in the Conditional Format dialogue for N2.

**dimitrz** · 09-12-2008, 08:19 AM

Forgot to mention why I need the formula to check only the first column for data.

Since Column B/C/D/E has some data always then the present formula results in an error wherein the Column “N” Priority goes RED through out – just as if using the Conditional Formatting without the formula – so we are back to square one.

I found this problem when I tested it in the real tracker in terms of column positions which is closer to the latest on attached in Joseph’s Excel forum.