No problem then, though I'm struggling to rationalise your 0-2 range when
subtotalling numbers in the region of 6-20. Anyway, with your data subtotalled,
and assuming your first piece of data starts in A4. select A4:P-end_of_range,
then do Format / Conditional Formatting, 'Formula is' and then pop in the
following:-
=AND(RIGHT($A4,5)="Total",A4<1,A4<"")
Now choose a nice yellow to go with it, and then hit the OK button.
Now hit 'Add' and then input:-
=AND(RIGHT($A4,5)="Total",A41,A4<"")
and choose a red. Hit Ok till out.
The A4<"" at the end means that it will not be applied to empty cells.
Note the $ signs on the first reference, and then NO dollar signs on the
others - It MUST be like this.
--
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
...
Yes, using the Excel 2000 Subtotal function from the Data
menu, based on each change in "full name" (column A), Use
function = Sum. Subtotal is added at D2:P500. Each
subtotalled row begins "Full name" Total (ex. Smith John
Total). This is the default format for the Subtotal
function, I've not applied the labelling. Subtotal is
added at D2:P500.
Here is an example, beginning column A, B, C, D, etc:
Full name DT PN 10/03 11/03 12/03 1/04 2/04
Smith John ab cd 21 22 23 24 25
Smith John ab cd 21 22 23 24 25
Smith John ab cd 21 22 23 24 25
Smith John Total 63 66 69 72 75
Jones Joe ac de 19 20 21 22 23
Jones Joe ac de 19 20 21 22 23
Jones Joe ac de 19 20 21 22 23
Jones Joe ac de 19 20 21 22 23
Jones Joe ac de 19 20 21 22 23
Jones Joe Total 95 100 105 110 115
BR,
Randy
-----Original Message-----
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
.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.530 / Virus Database: 325 - Release Date: 22/10/2003