Automatic Highlighting of Cells

[Bob Myers]

If a control number is entered in a Cell on a Worksheet, is there a way to
have that same number of Cells automatically highlighted (interior filled)
elsewhere on that sheet?

The number of cells highlighted should automatically change if the control
number is changed.

I don't believe there is a way to do it with conditional formatting, and a
macro would be after the fact.

Although I've written a number of macros and UDFs in separate Modules, I'm
not familiar with the coding of individual Worksheets in the VB Editor.

Respectfully submitted,
Bob Myers

 

[Glenn]

If a control number is entered in a Cell on a Worksheet, is there a way
to have that same number of Cells automatically highlighted (interior
filled) elsewhere on that sheet?

The number of cells highlighted should automatically change if the
control number is changed.

I don't believe there is a way to do it with conditional formatting, and
a macro would be after the fact.

Although I've written a number of macros and UDFs in separate Modules,
I'm not familiar with the coding of individual Worksheets in the VB Editor.

Respectfully submitted,
Bob Myers

Select the entire sheet and then use the following for conditional formatting
(select the fill pattern desired):

=AND(ROW()<=$A$1,COLUMN()<=$A$1)

Put your "control number" in A1. Some variation of this could work for you.

 

[Bob Myers]

Glenn, thanks. As usual, I didn't make myself clear. Specifically:

Put a 7 in Cell(B4), then have the Range(K11:K(10+B4)) automatically
highlighted.

Whatever number the user puts in B4 (with Validation), I want to draw his
attention to where and how many more cells he needs to fill in.

Bob Myers

 

[Glenn]

Same concept. Select column K and use this for conditional formatting:

=AND(ROW()>9,ROW()<=$B$4+9)

 

[Bob Myers]

Glenn, thanks for the help. I've spent the better part of the day on it and
have a couple observations. (I had some other constraints that I hadn't
mentioned.)

When I select Col K for conditional format, paste in your test (below), and
set a fill color, the entire column gets the fill except for the cells of
interest. However, if I fill the column with the fill color, not only does
the area of interest gets the fill, but so does every other cell in the
column outside the area of interest that has a value.

I'm not entirely happy with what I've got, but I couldn't have gotten here
without your help.

R/Bob