Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Changing font colour in a formula to highlight errors

0

Hi. 

I have created the formula 

=IF(L1544<>M1544,P$1,P$2)
to check if the data in columns L and M matches. In column K I get a tick or an error taken from either P1 or P2. My formula works well, but the errors are not highlighted as I had hoped.

In similar sheets I have just used Conditional Formatting to fill the cells with red or green, for instance. For this sheet I think the layout will be much more pleasant on the eyes.

Ultimately, columns L & M will be invisible and only ticks will be shown as the errors will be corrected before printing. If the errors are in red my corrective task will be much easier.

Thank you for any help you provide.

excel
MS Office Version: Office365
posted by: MrEMann (rep: 20) on Mon Mar 11, 2024 at 5:08 pm
Answer
Discuss

Answers

0
Selected Answer

 Hello again MrEMann,

I have made a couple of modifications to your file (attached) which might achieve what you want. In the attached file I have given 2 options: font colour only (col Q) and font and cell fill colour (col R). In those columns (rows 1-15) the font is Wingdings2, size 16. Entering a uppercase "O" yields an "X", an uppercase "P" yields a checkmark. The in-cell formula is now: =IF(OR(L1="",M1=""), "",IF(L1<>M1,"O","P")).

If this solves your issue, please mark my answer as Selected

Cheers   :-)

posted by: WillieD24 (rep: 557) on Mon Mar 11, 2024 at 6:51 pm
Discuss

Discussion

Thank you, this works perfectly as I had hoped it would. Many thanks to John for his input. I had just managed to get 0/1 in red and green. I was about to accept I would be left with a blank column after correcting the red errors. I now have a sheet where everyone can I have checked and verified every row. Brilliant, as always and thank you once again.
MrEMann (rep: 20) Mar 11, '24 at 9:11 pm
Add to Discussion
0

Hi again MyEMann

You can use a version of your formula:

=IF(L1544<>M1544,P$1,P$2)

in Condtional Formatting (based on a formula).

In the attached, revised example sheet (you provided), I set the formula for cell-based conditional formatting of cell K1 to be:

=IF($L1<>$M1, 1, 0)

so that produces 1 (TRUE) when the values in columns L and M don't match. For that condition, I set the text colour to red.

Note that the $ signs fix only the columns so I could then carefully change the "Applies to" portion of that rule to be:

=$K$1:$K$15

so all the example range is covered.

For completeness, I duplicated that rule and changed the formula of the duplicate to be:

=IF($L1=$M1, 1, 0)

and set the text colour to green.

Hope this fixes your problem. If so, please remember to mark this Answer as Selected. Thanks in advance.

posted by: John_Ru (rep: 6142) on Mon Mar 11, 2024 at 6:12 pm
Discuss

Discussion

@John
Once again we have been providing an answer at nearly the same time, you just beat me to the punch (LOL). We have provided similar but slightly different approaches. MrEMann is bound to like one of them.
Cheers   :-)
WillieD24 (rep: 557) Mar 11, '24 at 6:56 pm
@Willie - true! Glad the user selected your Answer - it has the advantage of not needing two reference cells for the ticks and crosses- well done. 
John_Ru (rep: 6142) Mar 12, '24 at 2:09 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login