CODEWITHSUNDEEP
excelconditional-formatting
Moacir
Moacir

Reputation: 627

Formula returns true, but formatting doesn't happen

I have the following data style:

My data

I need to format the columns background color in such a way that, if the header (merged cell) is found on another sheet, it should change the 3 columns color.

So, I did this formula

=IF(IFERROR(MATCH(INDIRECT(ADDRESS(1;INT(COLUMN()/3)*3));Plan2!$A:$A;0);0);TRUE)

As you can see on the screenshot, it returns "VERDADEIRO"(True) or "FALSO"(False) when on a cell. However it doesn't work when I put it on the conditional formatting.

I want to put that formula on the Conditional Formatting (Or something like that formula) and make it work. Any ideas why it doesn't work?

*The formula was originally in Portuguese, to translate it to english, I've uploaded to google sheets, since it auto-translates, to avoid errors

Upvotes: 0

Views: 207

Answers (1)

Moacir
Moacir

Reputation: 627

It looks like there is a bug in excel regarding INDIRECT with Conditional Formatting.

Based on this stackoverflow answer, I replaced the INDIRECT with an INDEXalternative.

The current formula is:

=IF(IFERROR(MATCH(INDEX($C$1:$ZZ$1;;INT(COLUMN()/3)*3);Plan2!$A:$A;0);0);TRUE)

Upvotes: 1

Related Questions