Conditional Formatting

[nathanmav]

Hi everyone i have a conditional formatting formula it change color when the status of a document is "A or B = white, C = Red, UR = Orange & NS or For Signature= Dark Orange".
My problem is the status C it will change the color when i enter B but how can i remove the color if i did not change the status instead i refer it to other reference which is approve.

Apologies if my explanation is not clear.. kindly check my attached sample: https://drive.google.com/file/d/0B6RmsSizixYMbnhaTFZXVWtIbHM/view?usp=sharing.thanks hope someone help me to figure out this problem.. again thank you in advance!

 

[nathanmav]

This is great james006.. thank you! i have question regarding the formula you used is this array? and also the total number of status C should be 14 not 13. check the row 70 the status is still C it should be highlighted with color red. and some C that is closed already is highlighted by red it should be white.

like for example:

HCCR/RI/ME/FF/043	0 C
HCCR/RI/ME/FF/043	1 B

<colgroup><col><col></colgroup><tbody>
</tbody>

this reference will be white because they are closed and approve already.

 

[James006]

Before getting in the red/white color ... issue ...

I would like to know how you get to 14 ... since they are 87 items in the C Category ... out of which 74 are duplicates ... so 87 - 74 = 13 ...

 

[nathanmav]

yes your right the total of status C is 87 but when i count manually the total is 18-4 which i refer to other approved reference = 14.


This the row that total 14:

Ref. No	Row No.
50	69
51	70
140	176
148	185
193	233
359	416
360	417
388	447
428	502
502	584
507	590
531	614
546	629
576	660

<tbody>
</tbody>

 

[James006]

I understand you are counting manually ...

But what really matters is the rule ... you are applying to include or not include any given C item ...

From a total of 87 ... some 26 are resubmitted a second time with the same number ... so 26 divided by 2 ... is 13 ...

 

[nathanmav]

your right i figured it already why i count it 14 i think its because of the row 447 that status is UR. but How about the highlighting the row? meaning highlighted should be 13 also right. thanks

 

[James006]

Glad we could fix the initial issue of the rule to be applied ....

We can now move on ... and tackle the highlighting of the rows ...

Out of the 87 C items ...

Do you only need these specific 13 rows to be shown in red .... or are they other C items to be included ...???

If we are to focus on these specific 13 C items ... which ones do you actually want to highlight ... the ones with Rev. 0 or the ones with Rev.1 ???

 

[nathanmav]

yes it should be the specific 13 rows.. and the latest should be highlighted meaning if rev 0 don't have rev 1 that the latest. thank you!

 

[James006]

Hello,

Attached is your file Version 3 ... with the red highlight only for the specific 13 rows ...(duplicated C items Rev.1) ...

Test NathanMav V3.xlsx

Hope this helps

 

[nathanmav]

Thank you for your reply james006,

i think we have some problem the highlighted row is already approved meaning it should be white color. and what do you mean of X you put in the column Q? that X you put is the row that should be highlighted in color red and also the total of Status C. thank you again!

 

[James006]

Sorry for all the X in Column Q ... I forgot to remove them after the test ...

Regarding the conditional formatting ... the 13 rows shown in red ... are the ones you said should appear in red ...!!!

So I do not understand your comment ...