0

I'm interested in formatting a row of cells ranging from "A" to "U" whenever text is added to the $A$<number> cell.

I've been playing around with the Conditional Formatting Rules Manager and have a rule set to the formula ISTEXT("A"&ROW())=TRUE and if I statically set cells in the "Applies to" section everything works fine.

Is it possible to apply a formula to the "Applies to" section?

DavidPostill
  • 153,128
  • 77
  • 353
  • 394
txcotrader
  • 123
  • 5

3 Answers3

1

No, it is not possible to use formulas in the Applies To box, at least not as you wish to.

You can use lots of functions in formulas in that box, even Named Ranges. But the moment you move off (just TAB will do), Excel converts everything to the literal cells being referred to. All the work is gone, never to return, leaving only the literal output at that moment for the formula/s and/or Named Ranges used.

You COULD put the formulas desired into one or more Named Ranges and use relative references as needed to achieve any result you desire. Then create a macro that updates the Applies To on any basis you wish: at file opening, whenever a button you create is pressed, on any change at all to the spreadsheet, however often or automatically you find you need, Excel has a mnechanism. When it finishes, moments later, your Applies To will have been updated and so should be the display based upon it.

(But with the literal output which will again and again need updating.)

Seems like such a little thing would make no real impact even if firing on every change to the spreadsheet, so this would probably provide something close enough to the Applies To being actually dynamic as to make no difference.

Er... probably... I have not tried it so...

Jeorge
  • 41
  • 2
0

You can select all your Data that you want to apply conditional formatting to and use this formula: =ISTEXT($A1)
it will work for each row with the corresponding A
No need to insert Formula in Applies to

Sam
  • 884
  • 6
  • 11
  • Sam, Thanks for your response! Unfortunately, I'm trying to avoid 2 things: First, having to select data as I'd like to keep the sheet dynamic. Second, only format a selected range of cells. – txcotrader Apr 27 '17 at 17:50
0

You can insert a formula in the "Applies To" field, e.g. "=INDIRECT("B"&C1&":B"&C2)". C1 contains "11" and C2 contains "365".
The problem is that the formula is resolved and converted into the actual range, e.g. "=$B$11:$B$365".

I described a workaround to a similar problem in my answer to another question.

Tobias Knauss
  • 361
  • 1
  • 9