Conditional Formatting Help! Multiple Conditions across multiple worksheets

[SSWABY]

Hi All, 

Can anybody help with the error I am getting when trying to format some conditions on a document I am using. I've attached an example of the three scenarios and a print screen of what I have set up in the actual document but for some reason not all scenarioes are working as they should. 

 

[#2]

@SSWABY 

You've had over 50 views and no replies. I think you'd be more apt to get a reply if you were to post a copy of the actual spreadsheet. Put it on OneDrive or GoogleDrive and paste a link here that grants access.

I will offer the observation that Conditional Formatting can be tricky; I often find I need to go through multiple iterations, a lot of trial and error.....so if you are inclined toward wanting to figure it out, just be aware that it can take time. You might--if you've not already done this--start with the simplest set of conditions, adding one more at a time and testing along the way. The other possibility is to have a helper column or two in which the conditions are tested, and let conditional formatting  just read the results in those helper columns, rather than trying to build everything into the conditional formatting rules themselves.

[#3]

I agreed with the previous response from @mathetes. You should upload the file on Drive and share the link.

As per the screenshot, it's bit difficult to guess the reason of error.

[#4]

Thanks for the feedback and advice @mathetes. Unfortunately I'm unable to provide a copy of the actual, document due to commercial reasons and GDPR around what it contains. 

I shall keep trying to resolve through trial and error and hopefully sombody who specialises in conditional formatting, can write out the formula as necessary to produce the outcomes as described and I can copy and change the cells referenced. 

[#5]

@SSWABY 

It's not necessary to provide your actual file, sample one with information which is already on screenshot plus fake data from another sheet, if it used in the rule and only such one, is enough.

On screenshot it looks like you try to highlight range C2:D4, however the rule is applied only to one cell $J$4. Formula in rule iterates nothing since it uses absolute references for $H$42 and $C$42. Why?

Other words it's not clear which logic connects C2:D4, $J$4, $H$42 and $C$42.