Highlight Cell if Reference is Blank but Display 0

Hi everyone. Would appreciate a little help with my problem.

Please see attached.

Data sheet
- this contains data to use. The cells highlighted in blue are those that have to be filled out. The rest the user should not touch. Sometimes, it is the quantity that has to be filled out, other times it is the rate.

Upload Sheet
- This sheet directly references the data sheet, and if it detects a blank Qty or Rate, it will return a 0. We use this sheet to upload to our accounting system. It does not recognize blanks, so blanks have to be 0.


Problem:
For those cells in the Upload Sheet that references a blank in the Data sheet, I would like them to display a 0 but also be highlighted in RED. The result should look like the Upload Sheet Desired Result sheet. Is there a a way to do this in VBA, or better yet just a simple formula?

Any assistance would be greatly appreciated. Thank you.

Did you try to fix the problem using conditional formatting?

Click on the cell that you want to highlight in RED when it has value as '0'. Go to Conditional formatting and set the condition as "if cell value is equal to zero then fill the cell with background RED". I got the below code when I recorded the steps that I explained above.

Please Login or Register  to view this content.

Hope this helps.

Regards,
Kumarapush123
OfficeTricks.com - Excel VBA Macro - Tips and Tricks

I could do conditional formatting, but the problem is when the reference is a legitimate 0. Like if the QTY that was entered in the Data sheet really was a 0 (and not a blank), then the Upload Sheet shouldn't highlight in red. It should only highlight when the QTY is blank ... but still display as 0. Is that possible with conditional formatting?