conditional formatting colors

[tania]

I need to somehow reflect drops in collections, but there
is no set beginning amount to put in the formula bar. I
need to highlight in bold or color decreases on a monthly
basis and by quarter when drops of three consecutive
months happen, I have tried everything, but nothing seems
to be working.

The spreadsheet is setup where as follows:

a b c d e f g H
name tume state desc rep month revenue collection

it continues down with all of Jan. going down from column
a4:a310 and then skips rows and begins Feb. stats
a312:a624 and so on.

I've tried the following conditional formatting cell value
is by highlighting column G and then using cell value is
and then greater than 30days and 60 days and so on but
doesn't work.

Thanks

 

[Frank Kabel]

Hi
you may post some data rows of your file (also plain text - no
attachment please) and describe a little bit more how your formating
rules should be applied

 

[David McRitchie]

Hi Tania,

I think you would need to make up another spreadsheet
with the name Jan Sales, Feb Sales, etc.
on one row and use that as your report and not try to
do the C.F. on the original sheet.
--
additional information:

If you are trying to enter something that compares
cell A2 with 312 I really don't know how you are
going to highlight each row involved. Also the
difference between a4 and A312 is 308 and
the difference between A310 and A624 is 314 so
your data. So obviously the names for each month
varies.

My page on Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm
pay particular attention to the cells that are highlighted
and the active cell, when you enter your formula.


HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[David McRitchie]

Hi Tania,
The easiest way to put your data into another
sheet would be to create a Pivot table

Data, Pivot table
select your data columns A through G
another sheet
Finish
drop name into (Col A) into Row fields
drop Month (Col F) into Column fields
drop Revenue (Col G) into data area
Right click on Count of Revenue,
Field settings, change to Sum

Conditional Formatting Column C5:Cnn
= C5<B5
Conditional Formatting Column D5:Gnn (or D:xnn)
=OR(D5<B5,D5<C5)

To Refresh your table, one way might be to create an Event
macro that you would invoke by double-click anywhere on the sheet
To install the macro for the sheet: Right click on sheet tab,
View code, place the following ...

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
'-- modified from Harold Staff's macro
'-- http://www.cpearson.com/excel/pivots.htm
Dim iP As Integer
Cancel = True 'Get out of Entry mode
Application.DisplayAlerts = False
For iP = 1 To ActiveSheet.PivotTables.Count
ActiveSheet.PivotTables(iP).RefreshTable
Next
Application.DisplayAlerts = True
End Sub

More information on Pivot Tables
Harold Staff in Chip Pearsons pages
http://www.cpearson.com/excel/pivots.htm
Debra Dalgleish (her site is Contextures)
http://www.contextures.com/tiptech.html her index
http://peltiertech.com/Excel/Pivots/pivotstart.htm
More links to Pivot Tables
http://www.mvps.org/dmcritchie/excel/sumdata.htm#pivottables
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi Tania,

I think you would need to make up another spreadsheet
with the name Jan Sales, Feb Sales, etc.
on one row and use that as your report and not try to
do the C.F. on the original sheet.
--
additional information:

If you are trying to enter something that compares
cell A2 with 312 I really don't know how you are
going to highlight each row involved. Also the
difference between a4 and A312 is 308 and
the difference between A310 and A624 is 314 so
your data. So obviously the names for each month
varies.

My page on Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm
pay particular attention to the cells that are highlighted
and the active cell, when you enter your formula.


HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi
you may post some data rows of your file (also plain text - no
attachment please) and describe a little bit more how your formating
rules should be applied