conditional formatting with multiple rows

Hi and welcome to the board,

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

hi arthurbr thank you for welcome and advice

I have attached a dummy workbook with how it is at the moment, with only the highlighting of text, if the text reads "FAIL"

if any clarification is needed, or anyone has questions, or cannot understand my English, please feel free to ask!

Originally Posted by teelim

Hi,

I hthink this is what you want.

For Conditional Formatting, I selected the whole column and formatted it to Font Color = Red when Cell value = FAIL

I created a formula in "Comments" column to show "More Than Once" if the person's name as appeared before.

Hope this helps.

Hi teelim, sorry for confusion, let me clarify.
some people are on-site many times, so will appear many times in the list, but not always as a fail (I hope!) so I want the colour to change (or the comment to appear - good thinking with that!) if that person has more than one fail against their name
so for instance, if "mr person" appears seven times in the list, each time with a PASS, then nothing happens, but if "someone else" appears four times, and two of them are FAIL, then have the FAIL cell background change colour to red (and black text)

It is not clear to me what you want highlighted if a person fails more than once. I assumed it is the name.

See attached, and don't hesitate to come back if you need more info

sorry, I think this makes more sense...

in attached, "mr man" has failed more than once, so the 'fail' cell has been highlighted.
I want to be able to have that happen automatically - so if a person fails once, the text 'FAIL' turns red, if they fail again (>1) then the text 'FAIL' stays red, but the cell is highlighted (like in attached)

I hope that makes more sense!

To keep things simple I added a helper column to count the nr of occurrences of FAIL for a person, then used the column to implement the CF

thank you so much arthurbr!

My pleasure

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

My pleasure

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

hi arthurbr,

I've just tried using that but found one small problem... the formula you used is for a set range of cells, but I need it for the entire column... I changed it from:
=SUMPRODUCT(($B$3:$B$10=B3)*($D$3:$D$10="fail"))
to:
=SUMPRODUCT(($B:$B=B3)*($D$3:$D$10="fail"))
but now the cell text says #NUM!
I tried changing it instead to:
=SUMPRODUCT(($B$3:$B$99=B4)*($D$3:$D$10="fail"))
and now get #N/A! as the cell text
I presume this is because those cells are empty... is there anyway around this?

sorry, I've realised my mistake... I forgot to change the values for the D column!
=SUMPRODUCT(($B$3:$B$999=B4)*($D$3:$D$999="fail"))

Glad you found a solution. SUMPRODUCT in versions prior to 2007 do not accept entire columns like $B:$B, and the ranges should have the same length, as you found out.
Extensive info on this function at http://www.xldynamic.com/source/xld....T.html#classic