conditional formatting colors

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

0
anonymous (74722)
8/8/2004 8:11:23 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
388 Views

Similar Articles

[PageSpeed] 14

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

--
Regards
Frank Kabel
Frankfurt, Germany


tania wrote:
> 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

0
frank.kabel (11126)
8/9/2004 5:47:33 AM
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

"Frank Kabel" <frank.kabel@freenet.de> wrote in message news:eIlZCRdfEHA.2908@TK2MSFTNGP10.phx.gbl...
> 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
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> tania wrote:
> > 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
>


0
dmcritchie (2586)
8/10/2004 2:22:01 PM
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

"David McRitchie" <dmcritchie@msn.com> wrote in message news:Ovj9sXufEHA.720@TK2MSFTNGP11.phx.gbl...
> 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
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message news:eIlZCRdfEHA.2908@TK2MSFTNGP10.phx.gbl...
> > 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
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> >
> > tania wrote:
> > > 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
> >
>
>


0
dmcritchie (2586)
8/10/2004 4:08:28 PM
Reply:

Similar Artilces:

how to format :send picture with message: as a default
each time i sent a graphic i must go to the format and click "send picture with message." how do I make this a default so it is there permanently.I am running on a Windows XP and am using Outlook Express for my mail. LYNNY <LYNNY@discussions.microsoft.com> wrote: > each time i sent a graphic i must go to the format and click "send > picture with message." how do I make this a default so it is there > permanently.I am running on a Windows XP and am using Outlook Express > for my mail. Ask in an Outlook Express newsgroup. -- Brian Tillman ...

Color Picker in Excel
Hi All, I will appreciate if someone can guide me to do a color picker using VBA in excel. I need to display the 57 colors of the palette and get the color index returned to put that value in a cell. A simple box with colors to do this using userform on xl's inbuilt dialog will be fine. Looking forward to all your help ! Thanx and regds Shuvro '-----------------------------�------------------------------�-------------- -- Function GetColorindex(Optional Text As Boolean = False) As Long '-----------------------------�------------------------------�-------------- -- Dim rngCurr...

Outlook 2007 256 Color Mode
Just returned home from a business trip, opened up my Outlook 2007 and it was UGLY! Looks like its running on 256 color mode or something. Running on Microsoft Vista / Office 2007 / Dell GX620 pc. It was working before I left. Not sure what cause or solution is. Tried doing a Office Repair...no affect. HELP! If you have a solution, please e-mail me at mcsims@gmail.com as I may loose this site location. Thanks...Michael Is it ONLY Outlook that looks that way? -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com <mcsims@gmail.com> wrote in message ...

Change cell background color based on content that results from li
This could be very simple, but I will lay the groundwork first. I have a schedule spreadsheet that I import data to from a web based program. There are existing filters to remove all formatting of the data and remove that which we do not use. What remains is a non formatted sheet that other workbooks link to so we can produce daily sheets. Now the data that is linked on the other sheets may be for example the number 150. It appears throughout the sheet and I would like to color any cell that contains the number 150. The problem is I can't search for 150 because it reall...

FORMATING COLUMNS..... HELP
I need to format columns to allow only 7 characters and the rest of the data to go into column B -- JTEFUN "JTEFUN" <JTEFUN@discussions.microsoft.com> wrote in message news:6C5C7A99-E83F-430E-9576-6D1DB57B9311@microsoft.com... >I need to format columns to allow only 7 characters and the rest of the >data > to go into column B > -- > JTEFUN > If you mean that if a user types a lot of data into the one cell and that when they reach 7 characters the rest of the data is automatically inserted into the next cell, then I don't think you can do that....

BULK Conditional Formatting
I've read the posts on conditional formatting for cell colour based o another cell's value (eg. set the conditional formatting to "formul is" and then "=A1>0" and set the colour as red / blue / whatever...) however wondering if I can do this for an entire column withou individually changing the conditional formatting for each cell one b one (as there are over 400 rows). Basically I need a formula that reads the contents of column B for th particular row that is active. Can anyone help -- Rob Moyl ---------------------------------------------------------------...

Format Problem
I have a report of a crosstab query. I formatted the left column of the report with Text Align set to Right. The report is chopping off the last character of the term, even if I set the Right Margin to 0.5". Doing so just puts space between the right edge of the border and the end of the chopped word I have played with all of the format settings in the properties with no success. If I set Text Align to Left the word is not chopped, but I would prefer Text Align set to right. Is there a way to fix this format issue with Text Align set to the Right? -- Message posted via http://www.acce...

Conditional Statements and Time Format
Hello, I have a spreadsheet where I need to calculate a range of time in a cell and display a value in another cell. Example is in cell F2 I have a time displayed of 15:34, and in the calculation window it displays as 3:34:00 PM. In Cell F3 I want to display one of three things, "1st shift", "2nd shift" or "3rd shift". Is it possible to use the conditional statements to give me the value of "1st shift" when cell F2 is between >= 07:00 and < 15:00? I can't find anything that speaks to getting thee range information from time. Thanks, Brian ...

Rich Text formatting in Access 2007
In Access 2007, is it possible to add rich text formatting to text in a memo field (or mixed formatting within any text field). I have just upgraded to the new version of Access, and it appears that this feature still has not been added. Is this correct? (If so, why?) If it is not possible to format individual words within a field in Access, , is there a simple, free add-in that will enable Access to do this kind of basic formatting, such as adding italic, bold, and underlining? Thanks very much for any information you may have. Bob Rich Text format for Memos in: Tables - Look in t...

formatting cell in vlookup
HELP I have an excel spreadsheet that is e:mailed to me by my home office. I use the vlookup feature to find the value in cell b from the information in a Proble At times when the answer is N/A and I go to the e:mailed spreadsheet to see if that number is really not there, it is in the e;mailed spreadsheet. If I type over that number in the cell on the e;mailed spreadsheet, then the formula will work in the target spreadsheet, sometimes This e;mailed spreadsheet is at a shared location on a servier, and the items I fix in the e;mailed spreadsheet on my desk top, are not available in any ...

Conditions on form calculated textbox
Hello, In a form called Schedule (based on the table Schedule) I have the following textboxes that are bound to the table Schedule: Time In, Time Out, Time Off, Anomaly and Overtime I also have an unbound textbox called HRS that gives me the result of the following equation: =[Time Out] - [Time In] - [Time Off] - [Anomaly] + [Overtime] The above result gives me the daily hours that someone worked. The formating is decimal, as an example. 16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0+ 3.0 = 10.0 I also added another figure for lunch the hour [16.5 (4:30) - 7.5 (7:30) - 1.0 - 1.0...

Copy a formula formatted as Text In Excel
I have a UDF that returns the formula of a given cell in a text format. Is there anyway to have excel recognize that the result of the function is a formula and not text. Once I click on the cell, hit F2 to edit it and then enter, Excel realizes it's a formula and then calculates the value, Nope. The formula in the cell is going to be the UDF--not the what formula string looks like. You could add some more steps. Select the range (if more than one cell) Convert to values (edit|copy, edit|paste special|values) and finally edit|replace what: = (equal sign) with: = replace all And exce...

Conditional Concatenate
Hi all. Its been a while since I was last here. I'm wondering if its possibl to devise a formula which concatenates a sentence in column B each tim there's an occurence in column A ? Thanks T -- Message posted from http://www.ExcelForum.com One way: B1: =IF(A1<>"",A1 & " starts this sentence.","") copy down as far as necessary. In article <twaccess.16eioe@excelforum-nospam.com>, twaccess <<twaccess.16eioe@excelforum-nospam.com>> wrote: > I'm wondering if its possible > to devise a formula which concaten...

Color Scheme in Outlook 2007
Is there a way to change the theme / color scheme in Outlook 2007. When I installed it has defaulted to a light blue and yellow. Is there a way to change the colors being used in the app? Thanks, -Chris only to gray. it's done from word. From http://www.slipstick.com/emo/2006/up060525.htm#faq How can I change Outlook's colors? From the Office icon in the upper left corner of Word (replacing the File menu), choose Word Options, Personalize and switch color schemes. At this time there are only two colors to choose from - Windows XP blue and Vista slate gray. -- Diane Poremsky [...

Conditional Signatures
Is there some way to set conditional signatures? eg. If I am sending a message to someone within my company (on my exchange server) it will use a short signature but if I send a message to someone outside my company it uses another longer signature? ...

Custom or conditional format to highlight today's date?
Using Excel 2002, I have all 365 days of the year listed in column B, formatted as "03/14/01". When i open the workbook, I would like the current date to be bold and red. Can I do this with a conditional format? I have tried =Today with no success. Thanks Tonso "Tonso" <wthomasss@hotmail.com> wrote in message news:13885af2-777c-43ff-9684-1b5bda8d95d6@o6g2000yql.googlegroups.com... > Using Excel 2002, I have all 365 days of the year listed in column B, > formatted as "03/14/01". When i open the workbook, I would like the > current date to b...

Font Format
I have merged two different sets of data where one sheet has names in all caps and the other not in all caps. When I'm doing a pivot table, it is using the names as two different values. There is no option in the font format to make the non-cap names into all caps. How can I make the font all consistent so that it is not appearing as two different values. Thanks! "mira" <mira@discussions.microsoft.com> wrote in message news:6A30BAA4-8640-47C2-8B7F-9B777A6A66D6@microsoft.com... >I have merged two different sets of data where one sheet has names in all > caps...

Filter by multiple colors
In Excel 2007, I am trying to filter by two different colors. I can see how I can filter by just one color - can I filter by two colors? Thanks for your help, -- Michelle ...

Formatting Numbers to Text or??!!!
I have a spreadsheet that contains the following data: Date Account ID 5/14/08 5280-3-01 5/14/08 5280-3-01 5/14/08 5280-3-01 5/14/08 5280-3-01 5/14/08 5280-3-01 5/14/08 5280-3-01 5/14/08 1030-0-01 5/14/08 1030-0-01 5/14/08 2101-0-01 5/14/08 2101-0-01 5/14/08 2101-0-01 5/14/08 2101-0-01 5/14/08 2101-0-01 5/14/08 2101-0-01 5/14/08 2101-0-01 5/14/08 2101-0-01 When I save it as a .csv file it looks like this: Date Account ID 5/14/2008 3/1/5280 5/14/2008 3/1/5280 5/14/2008 3/1/5280 5/14/2008 3/1/5280 5/14/2008 3/1/5280 5/14/2008 3/1/5280 5/14/2008 1030-0-01 5/14/2...

Color coding data in line graphs
I am building several line graphs for 9 customers. Is there a way to assign a color for the data by customer without manually adjusting the line for each customer in each line graph? You could adapt this approach: VBA Conditional Formatting of Charts by Series Name http://peltiertech.com/WordPress/vba-conditional-formatting-of-charts-by-series-name/ - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ brewer wrote: > I am building several line graphs for 9 customers. Is there a way to assign > a color for the data by customer without manually adju...

Mail Merge Date Format Problem
Trying to merge in data from an Excel spreadsheet but when the date goes in, it is in US format rather than english format, i.e 08/31/04 rather than 31/08/04. Tried changing the format in Excel first which doesn't work and my PC is set up with UK date as standard. Any ideas how I can change it? Cheers Nick Hi Nick (anonymous@discussions.microsoft.com), in the newsgroups you posted: || Trying to merge in data from an Excel spreadsheet but when || the date goes in, it is in US format rather than english || format, i.e 08/31/04 rather than 31/08/04. Tried changing || the format in E...

Formatting and loss of emails/contacts/etc?
Hi folks, Have to format and reinstall everything ... XP PRO/OFFICE 02 PRO ... I have emails/contacts/etc that I would like to be able take with me when I reinstall ... I can save copies of Excel/Word files to another hardrive. Is there a painless way of saving Outlook email/contact/etc files? Thanks in advance, Debbie The file you need to back up is your Personal Folders file (*.pst) It's where all the mail, calendar, contacts etc are stored. Just copy and reuse this file after you reformat. Take a look at these pages for info on Outlook data backup: http://www.slipstick.com/c...

cell format #9
I have a spreadsheet that is used to track cases in a legal clinic. One of the cells tends to get fairly large and it seems that once you reach a certain point, you can still add to it, but it will not display in the cell any longer. It looks as if it stops wrapping to fit the cell. When the cell is highlighted, you can see the entire text in the formula bar, but it does not appear in the cell. Is there a way to eliminate a limit on text in any given cell? Hi there's a limit of a max of 1204 characters per cell. You can extend this limit if you manually insert some linebreaks with ...

Chart Source Data Formats
We currenlty use Excel Charts where the Source Data is linked to anothe workbook. The Number Formats are not carried over correctly from thi Workbook unless both Workbooks are open at the same time-however th values are not affected by this and do not require the other Workboo to be open. We need to be able to E-Mail the Charts with the correct Number Format without needing to open both Workbooks as the recipients will not hav access to the Workbook containing the Source Data as this contain other confidential information. The version of Excel we are using is Microsoft Excel 2003. Any help or...

What's the best format for pictures used in Publisher?
This thread suggests PNG is the preferred format: http://www.microsoft.com/office/community/en-us/default.mspx?dg=microsoft.public.publisher.prepress&tid=a70b836f-f21b-44c1-a361-66ba89766785&cat=&lang=en&cr=US&sloc=&p=1 What's the best format? 1. TIFF 2. PNG 3. JPG Also, is it better to LINK the picture or EMBED the picture? Mr. Analogy wrote: > What's the best format? > 1. TIFF > 2. PNG > 3. JPG PNG is best for non-photographic data or data that need to be modified many times. JPEG is a good format for photographic data that is not modified...