Thread: Using conditional formatting to highlight a row based on the sumproduct formula

I was given some guidance the other day with the sumproduct function. The below formula says tell me how many time 4 and 10 are in the same row in the data range i have specified.
=SUMPRODUCT(--(COUNTIF(OFFSET(B2:D2,ROW(B2:D4)-ROW(B2:D2),0),4)>0),--(COUNTIF(OFFSET(B2:D2,ROW(B2:D4)-ROW(B2:D2),0),10)>0))

Is there a way either through conditional formatting or another method were i can highlight the row if the formula applies which will make it easier to look over the data if there is a large amount of data? I have provided a screen shot to try and simulate what i am trying to acheive. (i have manually highlighted the counted rows)

thanks

Originally Posted by NBVC

One way:

Select entire range starting and go to Format|Conditional Formatting

Select Formula Is from 1st drop down and enter formula:

=AND(ISNUMBER(MATCH(4,$B2:$D2,0)),ISNUMBER(MATCH(10,$B2:$D2,0)))

where B2:D2 is the top most row range in your entire selection

Click Format and choose your colour pattern.

this is great thanks! can you make it apply to multiple rows? eg highlight every row where this occurs between row 2 and 4? i tried replacing $B2:$D2 with $B2:$D4 but it didn't work.