Conditional Formatting on Excel 2010
Closed
Clue J

Dec 2, 2014 at 11:31 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022  Dec 4, 2014 at 11:34 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022  Dec 4, 2014 at 11:34 AM
Related:
 Conditional Formatting on Excel 2010
 Microsoft office 2010 free download  Download  Office suites
 Excel date format dd.mm.yyyy  Guide
 Microsoft publisher 2010 free download  Download  Publishing
 Pdf and xps add in 2010  Download  Other
 How to clear formatting in excel  Guide
1 response
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Dec 4, 2014 at 11:34 AM
Dec 4, 2014 at 11:34 AM
Hi Clue J,
An array formula would be nice to use in this situation, but unfortunately they don't work with conditional format.
What you can do is use an array formula in an empty column and then refer to the outcome of that formula in your conditional format formula.
Use this array formula in an empty column (out of sight, on different sheet, hide column):
=SUM(IF($E3:$G3=$B$27,1,0))
Array formula's need to be confirmed by hitting Ctrl+Shift+Enter. If done correctly the formula will be enclosed by curly brackets.
The formula basically counts the number of times the value in B27 is present in the given range. So in conditional format you can say that if the value is bigger then 0, then color the cell.
I hope this is clear, let me know if it's not.
Best regards,
Trowa
An array formula would be nice to use in this situation, but unfortunately they don't work with conditional format.
What you can do is use an array formula in an empty column and then refer to the outcome of that formula in your conditional format formula.
Use this array formula in an empty column (out of sight, on different sheet, hide column):
=SUM(IF($E3:$G3=$B$27,1,0))
Array formula's need to be confirmed by hitting Ctrl+Shift+Enter. If done correctly the formula will be enclosed by curly brackets.
The formula basically counts the number of times the value in B27 is present in the given range. So in conditional format you can say that if the value is bigger then 0, then color the cell.
I hope this is clear, let me know if it's not.
Best regards,
Trowa