Is there anyway to select a cell based on whether or not the cell above it is highlighted using VB in Excel?

Question

I have two columns: A and B. In A, I have a list of 10 numbers. What i need to do is look at the value in cell 1A and skip that many cells in column B, then look at the value in cell 2A and shade that many cells in B and so on alternating shaded with not shaded. If, for example the numbers in column A were 1, 2, 3, 4...the first cell in column B would be unshaded. The cells 2 and 3 would be shaded. Cells 4, 5 and 6 would be unshaded and cells 7-10 would be shaded.

Ive managed to do this. The script works fine except when the value in column A is zero. The script looks at the row number of the cell location and runs an odd or even test. If the cell is in an odd position it skips that number of cells if even-it highlights that many cells. If there is a zero in an odd numbered cell than the script skips zero cells and continues on to the next cell. i now no longer have alternating highlights.

I thought that a possible solution would be to determine the action based on whether or not the cell before the active cell is highlighted or not instead of cell location. But I don’t know how to have excel distinguish a highlighted cell from one that is not highlighted.

If ActiveCell.highlighted Then don’t highlight the following cells…something like this.

Answer 1

There's a cell property .Interior.Color. For example if A1 is shaded light grey then Range("A1").Interior.Color is equal to 123632256 (at least that's the value in Excel 97.)

In the Immediate window of the VBA editor, you can enter:

? Range("A1").Interior.Color then hit Return and it will tell you the number which designates A1's interior color. In this way, you can find the number representing the shading you are using (after shading A1 with the color you are using)

With this number, you can test and take action based on a cell's shading.

For example, to test if the cell above the ActiveCell is shaded:

If ActiveCell.Offset(-1,0).
Interior.Color = Then...

Good luck!

Answer 2

There sure should be but your description is very limited, please add details as I completed a major course and I can not decide fully on the course of action to meet your outcome.
Please add more detail and I shall be able to look into it. Please add step by step or even an example.

Most likely it sounds like either:

An If statement
or
Vlookup

Or as you stated a macro created using visual basic assigned to a button.