SOLVED

Contributor

# Format row if formula returns a result

I have a workbook with 2 sheets that list devices purchased from 2 different companies - RESTCO and Conduit. There are 500 rows on the RESTCO sheet which won't change since I'm now doing business exclusively with Conduit. The Conduit sheet is growing as I am managing all purchases through them. When I enter new data on the Conduit sheet, I have a column with a formula (shown at the bottom of this post) that checks the RESTCO sheet to see if that same product exists on the RESTCO sheet. If it does, it pulls the # from the Order # column and puts it into a Reference column on the Conduit sheet. This lets me see if the product has been purchased before and find the details more easily. I've been adding additional columns to the Conduit sheet so the Reference column is now off screen. I figured I would add conditional formatting so that a row will be formatted in bold to make it more obvious without scrolling to the reference column. But I can't figure out how to configure the condition so it only bolds if the formula returns a result.

=IFERROR(INDEX('RESTCO'!\$A\$2:\$A\$501,MATCH(B2,'RESTCO'!\$B\$2:\$B\$501,0),1),"")

Any idea how to make this work?

TIA

~DGM~

10 Replies

# Re: Format row if formula returns a result

Let assume you range is A2:E10000 and formula returns values in column E. Select entire range, add conditional formatting rule with formula =LEN(\$E2) > 0 and apply Bold format.

best response confirmed by DGMalcolm (Contributor)
Solution

# Re: Format row if formula returns a result

``=NOT(ISNA(INDEX(RESTCO!\$A\$2:\$A\$501,MATCH(\$B2,RESTCO!\$B\$2:\$B\$501,0),1)))``

An alternative could be this rule for conditional formatting.

# Re: Format row if formula returns a result

The reference column on the Conduit sheet is R so I opened the Conditional Formatting rule and made the formula: ="LEN(\$R2)>0". The first row shows '101' which is the correct row from the RESTCO sheet. After applying the rule, nothing gets bolded. What else am I missing?

# Re: Format row if formula returns a result

Is it applied to entire range?

# Re: Format row if formula returns a result

Yes, it's applied to everything except the header row like this: =\$A\$2:\$T\$101

I've limited my sheet to 100 data rows for now.

# Re: Format row if formula returns a result

Hard to say without the file. please check my one if you find the difference.

And are you sure you use LEN(\$R2), not LEN(\$R\$2) ?

# Re: Format row if formula returns a result

I don't see any difference but it doesn't seem to do what I want. I tried the other suggestion on this thread from @Quadruple_Pawn and it worked.

~DGM~

# Re: Format row if formula returns a result

This worked! Thank you!!

~DGM~

# Re: Format row if formula returns a result

``=NOT(ISNA(MATCH(\$B2,RESTCO!\$B\$2:\$B\$501,0)))``

You may skip index function for the conditional formating.

# Re: Format row if formula returns a result

@Willy Lau  - Thank you for this. Much simpler.