1

In Excel Conditional Formatting, I can use Indirect("table1") (structured referencing) in the Rule (on the left) but not in the Applies to (on the right) where Excel auto-corrects "table1" (relative reference range, structured referencing) to an absolute reference range.

Is there any workaround? Because I need to apply to a dynamic (expanding columns) table.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Tin Amaranth
  • 683
  • 2
  • 12
  • 23
  • if you want VBA solution, then Create Dynamic range. Many ways for this, u can have lastrow and lastcolumn assigned to variable or you can select currentregion..etc., then follow the code in the this link. http://stackoverflow.com/questions/25660087/how-to-apply-conditional-formatting-for-only-visible-cells – Punith GP Oct 13 '14 at 11:06
  • An example will be this: https://app.box.com/s/e96khwqnipw5jcy7y4b1 – Tin Amaranth Oct 29 '14 at 11:03

1 Answers1

0

Where structured references are involved (a Table of the type to which "expanding columns" could be applied) the Applies to range may be set with a structured reference and even though this is automatically converted by Excel into a 'standard' range (with anchors) it does not restrict the application of Conditional Formatting to the range indicated when rows are added to the table because (despite the anchors) the Conditional Formatting range is automatically "expanded" as the Table columns are extended.

pnuts
  • 58,317
  • 11
  • 87
  • 139