CODEWITHSUNDEEP
excelvbaconditional-formatting
J.Counter
J.Counter

Reputation: 15

Looking to do conditional formatting based on a variable cell

The plan is to have a variable cell to tell the conditional formatting how many non blanks to colour. I.e. I would like to colour only the first 5 non blanks within a data range.

The other conditional formatting I wish to have is the same but rather than going top down I want it to go from the bottom of the range up. Is this possible?

Upvotes: 0

Views: 81

Answers (1)

FunThomas
FunThomas

Reputation: 29286

You can use conditional formatting for that, using a formula that calculates the number of empty cells from a start cell to the current cell.

The following formula assumes that the cell that tells how many blank cells should be coloured is cell A1 and the data is in column C:

=AND(C1="",COUNTBLANK(C$1:C1)<=$A$1)

Note that the range C$1:C1 contains a $ for the start row, not for the end row. With that, the COUNTBLANK will count the number of empty cells starting from row 1 to the actual row.

To use it "backwards", you need to know which is the last row of your data. In the following example, row 20 is assumed:

=AND(C1="",COUNTBLANK(C1:C$20)<=$A$1)

Update Misread the original post. To mark the first non-blank cells, use formula

=AND(C1<>"",COUNTA(C$1:C1)<=$A$1)

Similarly, to mark from bottom, use

=AND(C1<>"",COUNTA(C1:C$20)<=$A$1)

Upvotes: 2

Related Questions