Conditional formatting

New Contributor


Hi Excel Community,


I am trying to colour format a set of data in accordance to the category I have selected for each respective apt. Will anyone be able to recommend the best way for me to go about with this task please? Any help will be greatly appreciated. Thanks.



2 Replies
best response confirmed by liping123 (New Contributor)

@liping123 If the Apt codes column A are always sorted in ascending order, you could use a CF formulae like:

=LOOKUP(A14,$A$3:$A$8,$D$3:$D$8)=1 for dark green,

=LOOKUP(A14,$A$3:$A$8,$D$3:$D$8)=2 for light green etc.

Screenshot 2021-05-26 at 11.00.15.png

as demonstrated in the attached file.


If you are on a recent version of Excel that supports XLOOKUP, I would recommend to use that in stead. The formula would be similar, though you'd have to adopt is slightly.

Thank you so so so much! That was exactly what I was trying to do.