how to conditional format a cell that captures a certain criteria in an IF formula

[MyStix01]

Hi,

So I have this formula in my spreadsheet.

=IF(D7=0,0,IFERROR(1/(1/VLOOKUP($A7,'C - CC'!$A:$P,11,0)),D7+E$4))

And I'm just looking for a way to conditional format the cells where this criteria is true 1/(1/VLOOKUP($A7,'C - CC'!$A:$P,11,0)

Can someone help me with the conditional format formula to use?

Thank you!

[AliGW]

Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.

[AliGW]

That's better - thanks.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[shukla.ankur281190]

May be, You can add Isnumber Logic with the above formula in conditional formatting rule,

Please Login or Register  to view this content.

[MyStix01]

Hi Ankur,

Thanks for the response, it didnt worked though.

For easy reference, kindly see attached file.

Basically, the formula in cell E7:I11 is looking up if whether the dates are available in sheet C-CC.

And if they're not, then instead the formula manually calculates the date using the data from cell E4:I4.

And what I want to happen is to highlight those cells that were manually calculated using conditional formatting.

Hope that makes sense. Appreciate your assistance. Thank you!

[shukla.ankur281190]

Try in conditional formatting. Select E7:I11 and press Alt-O-D-> New Rule-Choose Use formula to determine which cell need to format-put below formula in formula section and choose format then okk

Please Login or Register  to view this content.

Note change header in sheet C-CC P1=PCI

[MyStix01]

Wow, it worked perfectly! Thank you so much!

[shukla.ankur281190]

Happy to help you thanks for feedback

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[MyStix01]

Hi Ankur,

Sorry if I have to changed it back to Unsolved, just wondering if you can twitch it a little. Kindly see attached file.

As my spreadsheet is meant to autopopulate everything and highlight details, I have to copy the conditional format formula all the way down.

And currently, what's happening is, the rows with no details are getting highlighted too when it should not. Sorry, I wasn't able to specify this before.

Appreciate your help once again. Thank you thank you thank you so much!

[shukla.ankur281190]

In such case you can try in conditional formatting =IF(A7="",FALSE,ISERROR(1/(1/VLOOKUP($A7,'C - CC'!$A:$P,MATCH(E$2,'C - CC'!$A$1:$P$1,0),0))))

[MyStix01]

In such case you can try in conditional formatting =IF(A7="",FALSE,ISERROR(1/(1/VLOOKUP($A7,'C - CC'!$A:$P,MATCH(E$2,'C - CC'!$A$1:$P$1,0),0))))

Hi Ankur,

Not sure what's wrong in my sheet but it's not currently working. The formula is giving me the same outcome

[shukla.ankur281190]

Check the attached file.

[MyStix01]

Check the attached file.

Just got to lock $A7 and it's working perfectly now. Thank you so much for all the help!