Need to apply conditional formatting to cells within
subtotalled rows in lengthy spreadsheets, and would prefer
to do so in a macro, as I will use this solution
frequently. Conditional formatting would turn cells red
for greaterthan values, yellow for lessthan values.
Subtotalling occurs after varying number of rows of data
(~6 - 20), and subtotalled cells occur in columns d-p.

Thanks!

When you say you will use it frequently, why does that drive a macro solution?
Conditional formatting is dynamic, and wuill change automatically as your data changes,
without any intervention needed on your part. If you give us the ranges and the limits
and criteria, we should be able to help you.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



wrote in message
...
Need to apply conditional formatting to cells within
subtotalled rows in lengthy spreadsheets, and would prefer
to do so in a macro, as I will use this solution
frequently. Conditional formatting would turn cells red
for greaterthan values, yellow for lessthan values.
Subtotalling occurs after varying number of rows of data
(~6 - 20), and subtotalled cells occur in columns d-p.

Thanks!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003

Thanks Ken.

This would be typical:
Range: D2:P500
Criteria: <1 cell turns yellow, 1 cell turns red
Limits: values will range from 0 - 2, disallow "---"

I'll generate these spreadsheets daily, format remains
consistent, but the data content and filename will
change. I was thinking that wrapping vb code in a macro
would work best in Excel. Is there a better way?

T-I-A,
Randy

-----Original Message-----
When you say you will use it frequently, why does that
drive a macro solution?
Conditional formatting is dynamic, and wuill change
automatically as your data changes,
without any intervention needed on your part. If you
give us the ranges and the limits
and criteria, we should be able to help you.

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------
------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------
------------------



wrote in message
...
Need to apply conditional formatting to cells within
subtotalled rows in lengthy spreadsheets, and would
prefer
to do so in a macro, as I will use this solution
frequently. Conditional formatting would turn cells red
for greaterthan values, yellow for lessthan values.
Subtotalling occurs after varying number of rows of data
(~6 - 20), and subtotalled cells occur in columns d-p.

Thanks!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date:
16/10/2003


.

Select the range concerned and do Format / Conditional formatting / Cell value is less
than 1 - Hit Format button and choose either a pattern or font colour to suit, then hit
OK. now click on the 'Add' button, and do the same again but choose 'cell value is less
than' this time.

As the data changes, the conditional formatting will automatically reflect the appropriate
colour. Just make sur ethat when you paste new data into the cells you choose paste
special / values, otherwise you wipe out the Conditional formatting.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Randy" wrote in message
...
Thanks Ken.

This would be typical:
Range: D2:P500
Criteria: <1 cell turns yellow, 1 cell turns red
Limits: values will range from 0 - 2, disallow "---"

I'll generate these spreadsheets daily, format remains
consistent, but the data content and filename will
change. I was thinking that wrapping vb code in a macro
would work best in Excel. Is there a better way?

T-I-A,
Randy

-----Original Message-----
When you say you will use it frequently, why does that
drive a macro solution?
Conditional formatting is dynamic, and wuill change
automatically as your data changes,
without any intervention needed on your part. If you
give us the ranges and the limits
and criteria, we should be able to help you.

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------
------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------
------------------



wrote in message
...
Need to apply conditional formatting to cells within
subtotalled rows in lengthy spreadsheets, and would
prefer
to do so in a macro, as I will use this solution
frequently. Conditional formatting would turn cells red
for greaterthan values, yellow for lessthan values.
Subtotalling occurs after varying number of rows of data
(~6 - 20), and subtotalled cells occur in columns d-p.

Thanks!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date:
16/10/2003


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003

That was my first effort at this problem, and thus the
reason I'm looking for help. :-)

These will be new spreadsheets, formatted by macro. I
only want conditional formatting applied to subtotalled
rows, and the row count will vary per subtotalled range,
as well as total of rows in the worksheet.

Can you help me with a formula to apply in the conditional
formatting function, or vba code in a macro that will find
subtotalled rows, per the range/criteria/limits described
below?

BR,
Randy


-----Original Message-----
Select the range concerned and do Format / Conditional
formatting / Cell value is less
than 1 - Hit Format button and choose either a pattern or
font colour to suit, then hit
OK. now click on the 'Add' button, and do the same again
but choose 'cell value is less
than' this time.

As the data changes, the conditional formatting will
automatically reflect the appropriate
colour. Just make sur ethat when you paste new data into
the cells you choose paste
special / values, otherwise you wipe out the Conditional
formatting.

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------
------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------
------------------



"Randy" wrote in
message
...
Thanks Ken.

This would be typical:
Range: D2:P500
Criteria: <1 cell turns yellow, 1 cell turns red
Limits: values will range from 0 - 2, disallow "---"

I'll generate these spreadsheets daily, format remains
consistent, but the data content and filename will
change. I was thinking that wrapping vb code in a macro
would work best in Excel. Is there a better way?

T-I-A,
Randy

-----Original Message-----
When you say you will use it frequently, why does that
drive a macro solution?
Conditional formatting is dynamic, and wuill change
automatically as your data changes,
without any intervention needed on your part. If you
give us the ranges and the limits
and criteria, we should be able to help you.

--
Regards
Ken....................... Microsoft
MVP -
Excel
Sys Spec - Win XP Pro / XL2K & XLXP

-------------------------------------------------------
---
------------------
Attitude - A little thing that makes a BIG difference
-------------------------------------------------------
---
------------------



wrote in message
...
Need to apply conditional formatting to cells within
subtotalled rows in lengthy spreadsheets, and would
prefer
to do so in a macro, as I will use this solution
frequently. Conditional formatting would turn cells
red
for greaterthan values, yellow for lessthan values.
Subtotalling occurs after varying number of rows of
data
(~6 - 20), and subtotalled cells occur in columns d-
p.

Thanks!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date:
16/10/2003


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date:
16/10/2003


.

This might get you started again (but I'd record a macro when I did it once to
get the correct colorindex numbers (and maybe the syntax for conditions).

Option Explicit
Sub testme01()

Dim myRng As Range
Dim wks As Worksheet
Dim myCols As Variant
Dim iCtr As Long

Set wks = ActiveSheet

myCols = Array(2, 4, 6)

With wks
Set myRng = .Range("A1:P" & .Cells(.Rows.Count, "A").End(xlUp).Row)
.Outline.ShowLevels rowlevels:=2 'adjust to match your data
For iCtr = LBound(myCols) To UBound(myCols)
With Intersect(myRng.Cells.SpecialCells(xlCellTypeVisib le), _
.Columns(myCols(iCtr)))
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlLess, Formula1:=1
.FormatConditions(1).Interior.ColorIndex = 27
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlGreaterEqual, Formula1:=1
.FormatConditions(2).Interior.ColorIndex = 19
End With
Next iCtr
.Outline.ShowLevels rowlevels:=8 'highest possible
End With
End Sub

And you'll have to specify your columns (in myCols). I had one subtotal, so I
showed level 2. Adjust if you apply data|subtotals multiple times.



Randy wrote:

That was my first effort at this problem, and thus the
reason I'm looking for help. :-)

These will be new spreadsheets, formatted by macro. I
only want conditional formatting applied to subtotalled
rows, and the row count will vary per subtotalled range,
as well as total of rows in the worksheet.

Can you help me with a formula to apply in the conditional
formatting function, or vba code in a macro that will find
subtotalled rows, per the range/criteria/limits described
below?

BR,
Randy

-----Original Message-----
Select the range concerned and do Format / Conditional
formatting / Cell value is less
than 1 - Hit Format button and choose either a pattern or
font colour to suit, then hit
OK. now click on the 'Add' button, and do the same again
but choose 'cell value is less
than' this time.

As the data changes, the conditional formatting will
automatically reflect the appropriate
colour. Just make sur ethat when you paste new data into
the cells you choose paste
special / values, otherwise you wipe out the Conditional
formatting.

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------
------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------
------------------



"Randy" wrote in
message
...
Thanks Ken.

This would be typical:
Range: D2:P500
Criteria: <1 cell turns yellow, 1 cell turns red
Limits: values will range from 0 - 2, disallow "---"

I'll generate these spreadsheets daily, format remains
consistent, but the data content and filename will
change. I was thinking that wrapping vb code in a macro
would work best in Excel. Is there a better way?

T-I-A,
Randy

-----Original Message-----
When you say you will use it frequently, why does that
drive a macro solution?
Conditional formatting is dynamic, and wuill change
automatically as your data changes,
without any intervention needed on your part. If you
give us the ranges and the limits
and criteria, we should be able to help you.

--
Regards
Ken....................... Microsoft
MVP -
Excel
Sys Spec - Win XP Pro / XL2K & XLXP

-------------------------------------------------------
---
------------------
Attitude - A little thing that makes a BIG difference
-------------------------------------------------------
---
------------------



wrote in message
...
Need to apply conditional formatting to cells within
subtotalled rows in lengthy spreadsheets, and would
prefer
to do so in a macro, as I will use this solution
frequently. Conditional formatting would turn cells
red
for greaterthan values, yellow for lessthan values.
Subtotalling occurs after varying number of rows of
data
(~6 - 20), and subtotalled cells occur in columns d-
p.

Thanks!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date:
16/10/2003


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date:
16/10/2003


.


--

Dave Peterson

What denotes a subtotalled row?? - Are you using data / Subtotals, in which case each
relevant row will begin with the word Sub Total?? If not, are you actually labelling them
with Total or Subtotal?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Randy" wrote in message
...
That was my first effort at this problem, and thus the
reason I'm looking for help. :-)

These will be new spreadsheets, formatted by macro. I
only want conditional formatting applied to subtotalled
rows, and the row count will vary per subtotalled range,
as well as total of rows in the worksheet.

Can you help me with a formula to apply in the conditional
formatting function, or vba code in a macro that will find
subtotalled rows, per the range/criteria/limits described
below?

BR,
Randy


-----Original Message-----
Select the range concerned and do Format / Conditional
formatting / Cell value is less
than 1 - Hit Format button and choose either a pattern or
font colour to suit, then hit
OK. now click on the 'Add' button, and do the same again
but choose 'cell value is less
than' this time.

As the data changes, the conditional formatting will
automatically reflect the appropriate
colour. Just make sur ethat when you paste new data into
the cells you choose paste
special / values, otherwise you wipe out the Conditional
formatting.

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------
------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------
------------------



"Randy" wrote in
message
...
Thanks Ken.

This would be typical:
Range: D2:P500
Criteria: <1 cell turns yellow, 1 cell turns red
Limits: values will range from 0 - 2, disallow "---"

I'll generate these spreadsheets daily, format remains
consistent, but the data content and filename will
change. I was thinking that wrapping vb code in a macro
would work best in Excel. Is there a better way?

T-I-A,
Randy

-----Original Message-----
When you say you will use it frequently, why does that
drive a macro solution?
Conditional formatting is dynamic, and wuill change
automatically as your data changes,
without any intervention needed on your part. If you
give us the ranges and the limits
and criteria, we should be able to help you.

--
Regards
Ken....................... Microsoft
MVP -
Excel
Sys Spec - Win XP Pro / XL2K & XLXP

-------------------------------------------------------
---
------------------
Attitude - A little thing that makes a BIG difference
-------------------------------------------------------
---
------------------



wrote in message
...
Need to apply conditional formatting to cells within
subtotalled rows in lengthy spreadsheets, and would
prefer
to do so in a macro, as I will use this solution
frequently. Conditional formatting would turn cells
red
for greaterthan values, yellow for lessthan values.
Subtotalling occurs after varying number of rows of
data
(~6 - 20), and subtotalled cells occur in columns d-
p.

Thanks!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system
(http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date:
16/10/2003


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date:
16/10/2003


.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003