Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Change Cell Color Given Cell Range as Text in A1

0

I have a spreadsheet in which I have used formulas to build a cell range to change the color of the cells.  Cell AP3 has the following Text: $B$10:$B$17.  I'd like to use that text to change the defined cell range color to red.

I like to avoid using VB as a solution.  Any advise on doing it within a formula? 

I tried Contidional formating but couldn't get it to work.

Any help you can give would be appreciated.

Answer
Discuss

Answers

0
Selected Answer

 Indeed it is conditional formatting that will do the job, meaning the formatting must be applied to the cells whose colour is supposed to change - a much bigger range than the one indicated in AP3, comprising all cells which might, at one time or another, be included in the range specified in AP3. Determine the cells to be formatted by the formula below.

=AND(AND(ROW()>=INDEX(ROW(INDIRECT($AP$3)),1),ROW()<(INDEX(ROW(INDIRECT($AP$3)),1)+ROWS(INDIRECT($AP$3)))),AND(COLUMN()>=INDEX(COLUMN(INDIRECT($AP$3)),1),COLUMN()<(INDEX(COLUMN(INDIRECT($AP$3)),1)+COLUMNS(INDIRECT($AP$3)))))

Basically, what this formula says is this. Apply the colour if ...

  1. A cell's row number >= the first row of the range defined in AP3
    AND the cell's row number <=  the last row of the range defined in AP3

    AND
  2. A cell's column number >= the first column of the range defined in AP3
    AND the cell's column number <=  the last column of the range defined in AP3.

It follows that the formula is laid out to permit AP3 to specify a range comprising multiple coloumns as well as multiple rows. Note that it isn't necessary to apply absolute references in AP3. B10:B17 will do the job as well as $B10:$B17 or $B$10:$B$17.

As a matter of interest, in a worksheet cell, =Row(B10:B17) will return the number 10, being the first row of the defined range. CF didn't accept this however, presumably because Row(B10:B17) actually is an array {10;11;12;13;14;15;16;17}. Therefore the above formula specifically extracts the first element of that array, using INDEX(ROW(B10:B17),1). Convoluted but necessary.

Discuss

Discussion

The CF selection I used was "Use a Formula to Determine Which Cells to Format.  I added your formula in the field "Format values where this formula is true".   I set the format color to red.  The "Applies to field range" is A10:AF130.  However, It did not change the color in range B10:B17.  I even removed the absolute references.
Any advise?
mmcginnis (rep: 2) Dec 7, '17 at 10:22 am
The formula doesn't mention B10:B17. This address might come from AP3. If it is the range specified there it is the range that should be coloured. If, instead, it is the range which is not coloured, the fault should be with your colour settings. The formula isn't capable of reversing colours.
BTW, I just tried to replace the address in AP3 with a named range and the formula works. This might enable you to create a data validation dropdown in AP3 where you list the names of ranges to highlight.
Variatus (rep: 4889) Dec 8, '17 at 10:35 pm
I was unable to get it to work.  The only way to show you what I'm working on is to email it to you if you'd be willing to look at it.
mmcginnis (rep: 2) Dec 18, '17 at 10:28 am
Please send a request to contact to eyetome@126.com. Don't send any workbook there. Your mail will be forwarded to me, I shall respond, and then you can send the workbook to me directly.
Variatus (rep: 4889) Dec 18, '17 at 9:54 pm
Add to Discussion
0

Use =INDIRECT(AP3) in your CF to refer to the range

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login