Fill color in Range between specified value

[jeff.p]

Hello,


Trying to find a way to fill in a color based on a specified value, up to the last entry.

ie. Within a range ("Range1")T1:T9, anywhere the value is "R" , fill the color red within column P (offset -5 columns to the left) up to the last entry.

..this is something I would like to initiate via button assignment using a VBA script.

eg:

Column..P,.....T

1..................B
2........Red.....R
3........Red.....T
4........Red.....R
5........Red.....V
6........Red.....M
7........Red.....R
8..................X
9..................X


Thanks for any help


~Jeff

[shg]

Why not use conditional formatting?

[jeff.p]

Thanks SHG,

I had tried to incorporate Conditional Formatting, however I can only get as far as changing the format of the individual cell.

I'm searching for a way to not only fill in the color between these values, but offset them as well

Any ideas to accomplish this using Conditional formatting?


~Jeff

[VBA Noob]

Select the range and enter

=$T1="R"

http://www.contextures.com/xlCondFormat02.html

or code. Right click sheet and select view code

Please Login or Register  to view this content.

VBA Noob

[shg]

For the conditional formatting in column P, select P1:P9 and use the formula,

=AND( ISNUMBER( MATCH("R", $T$1:$T1, 0) ), ISNUMBER( MATCH("R", $T1:$T$9) ) )

... and set the formatting you wish.

[jeff.p]

Thanks for the help!

VBA Noob , I liked the code you provided, It offsets and marks red for the appropriate value, however it only fills that adjacent cell.

ie:

.........
Red.....R
Red.....R
.........
.........
Red.....R
.........
.........

as opposed to

.........
Red.....R
Red.....R
Red......
Red......
Red.....R
.........
.........





shg,

Thanks, using the conditional formatting method worked great!



Thanks again for the help!

Jeff

[shg]

You're welcome. I edited my prior post to a somewhat simpler formula.

[jeff.p]

Hello,

I have 2 follow up questions regarding conditional formatting,... If I may ?


How can I modify the code to encompass a larger range?..
currently the range of the code is between T1:T9.
would like to adapt this to work between T16:T600 ? ( trying for several hours with unfavorable results)

Also when a new row is inserted, the conditional formatting no longer works. (my workbook continually increases/decreases in size) Is there a workaround for this.? Perhaps to incorporate a named range?

Thanks for any help


Jeff

[Paul]

Hello jeff.p,

You should be able to modify shg's conditional formula. First, select the range P16:P600, then click Format -> Conditional Formatting.

The new formula would be (I think):

Please Login or Register  to view this content.

As for the issue with inserting rows, if you go into Tools -> Options -> Edit tab, is 'Extend data range formats and formulas' checked?

[shg]

Thank you, pjoaquin, for stepping in. Jeff, his suggested change should work fine. Looking at it again, the formula could be simplified:

=COUNTIF($T$16:$T16, "R") * COUNTIF($T16:$T$600, "R")

[jeff.p]

Thanks, Paul, shg, for all the help!

worked out great!

Jeff

[jeff.p]

Hello.

A follow up to this quarry..

I'm using this conditional format code (compliments of "shg")

=COUNTIF($T$16:$T16, "R") * COUNTIF($T16:$T$600, "R")


It works great!...However my worksheet has become very slow.. I have several such columns with this formula, albeit
triggered by a different value : ie:


=COUNTIF($AN$16:$AN16, "A") * COUNTIF($AN16:$AN$600, "A")

=COUNTIF($AN$16:$AN16, "B") * COUNTIF($AN16:$AN$600, "B")

=COUNTIF($AN$16:$AN16, "C") * COUNTIF($AN16:$AN$600, "C")

=COUNTIF($AN$16:$AN16, "D") * COUNTIF($AN16:$AN$600, "D")





Is there a way to incorporate this as a VBA macro. One that I can assign to
a module and initiate via button assingment?

...I currently have these ranges as Named Ranges ie: "RangeA"(AD16:AD600), "RangeB" (AE16:AE600), etc.


Thanks for any help..

Jeff