Conditional Formatting where formula is included

On Sheet 1, cell D5 contains ='Pg1!C3 - this is pulling a value of 3 from a second worksheet, Pg1

I tried to set up conditional formatting on D5 (Sheet 1) based on the Rule Type - "Format only cells that contain"' a cell value of 3, (Cell Value = 3 and setting a colour of Orange), but that does not work.

Can you advise (hopefully without a macro) if there is a way for conditional formatting to work in this instance?

it should work, MY thoughts are is the value in the background sheet text?, or formatted to be 3 and is actually 3.1 or 2.9?

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hopefully that has uploaded a file entitled, "Test"

Value from 'Pg1!C3 is Text so in CF you need to change to ="2"

Or change C3 formula to
=IF(AND(H35>=0, H35<=25), 1, IF(AND(H35>=26, H35<=49), 2, IF(AND(H35>=50, H35<=74), 3, IF(AND(H35>=75, H35<=100), 4, ""))))

Numbers should not be surrounded by quotes. That makes them into text values as mentioned in post #4.

I recommend removing all quotes (that surround numbers) from formulas and Conditional Formatting.

On the original post i asked you if the values were text. they are so text <> a number so no format

=IF(AND(H35>=0, H35<=25), "1", IF(AND(H35>=26, H35<=49), "2", IF(AND(H35>=50, H35<=74), "3", IF(AND(H35>=75, H35<=100), "4", ""))))

you can see the formula returns a value as text, if you return it as a number
=IF(AND(H35>=0, H35<=25), 1, IF(AND(H35>=26, H35<=49), 2, IF(AND(H35>=50, H35<=74), 3, IF(AND(H35>=75, H35<=100), 4, ""))))

or you change the contitional format to deal with text