Conditional formatting alternative

I was able to highlight some cells using conditional formationg.
Unfrotunately the cells are not next to ehac other (there are blank
cells in the middle).  Since I don't need those blank cells I can just
delete them or sort the column so all the cells with information move
to the top, the problem is that when I finish sorting and the cells
move up I lose the format I applied on conditional formatting.

is there any other way to apply format (change color) to a cell
depending on 2 other cells?

This is what I have:
    Highlight in bold row 2 if A2 -a date- is between A1 -date #1- and
A2 -date#2-

However I want to maintain that format (bold highlight) even if I move
A2 to a separate column.  Does it make sense?

0
8/2/2006 4:34:43 PM
excel 39879 articles. 2 followers. Follow

2 Replies
491 Views

Similar Articles

[PageSpeed] 54

cfmartin76@gmail.com wrote:
> I was able to highlight some cells using conditional formationg.
> Unfrotunately the cells are not next to ehac other (there are blank
> cells in the middle).  Since I don't need those blank cells I can just
> delete them or sort the column so all the cells with information move
> to the top, the problem is that when I finish sorting and the cells
> move up I lose the format I applied on conditional formatting.
>
> is there any other way to apply format (change color) to a cell
> depending on 2 other cells?
>
> This is what I have:
>    Highlight in bold row 2 if A2 -a date- is between A1 -date #1- and
> A2 -date#2-
>
> However I want to maintain that format (bold highlight) even if I move
> A2 to a separate column.  Does it make sense?



Try to post a sample of your data and also the formula you used in 
condtional formatting...

-- 
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy 


0
fra68ve (124)
8/3/2006 3:45:42 AM
This is how it looks:

	     A	               B
1	5/12/2006	5/20/2006
2	5/15/2006	APPLE
3	5/25/2006	ORANGE
4	5/12/2006	APPLE

The formula is:
=AND($A2>=$A$1,$A2<=$B$1)  then the formating (bold).  Once I set thie
conditional formatting, rows 2 and 4 will be bold.  If I move cell B2
to a separate column I will lose the format.  How can I keep the format?

0
8/3/2006 3:35:52 PM
Reply:

Similar Artilces:

Cond Format: Expiry Date Alert
Does anyone know how to solve this one? I have passport expiry dates and I would like to conditionally format them so that 6 months before the passport expires, the cell containing the expiry date changes red. Thanks -- ChrisTMI ------------------------------------------------------------------------ ChrisTMI's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10342 View this thread: http://www.excelforum.com/showthread.php?threadid=481899 use conditional formatting "ChrisTMI" wrote: > > Does anyone know how to solve this one? > > I h...

Trouble formatting Mail merge Number field for european format
I have to mail merge a letter using XLS as datasource that has some currency fields and the output of the currency fields should be formatted to european format (eg: 1.234.567,99) Please note that decimal separator is comma and group seperator a dot. If could not find a suitable switch. Can someone please point me to right place? O/S: Windows XP MS Office 2003 The European format would be; 1,234,567.99 and not the decimal seperator being a comma "SRV" <SRV@discussions.microsoft.com> wrote in message news:91B4D91E-294F-4F6B-A131-743D3171A7E2@microsoft.co...

how do i type a time into a cell formatted for time?
I have highlighted a block of cells and used format-cell-time to format for time but when I try to type in a time using the 24 hr clock eg. 0800 it converts the time to a date. What am I ding wrong? You're doing nothing wrong - except for Text (which bypasses XL's entry parser), the display format has no effect on how an entry is parsed. For a way to accomplish what you're after, see http://cpearson.com/excel/DateTimeEntry.htm In article <B52639A5-5BA2-46F7-8407-DBA6554B21EE@microsoft.com>, Armadillo <Armadillo@discussions.microsoft.com> wrote: > ...

How to format cells in upercase entry
Hi I read the post for this question (8/8/2006) and Gord Dibben's answer works great. Original Question: I need to format a column so that all cell force any text entry to uppercase. Gord Dibben's Answer: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column > 8 Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False Target.Formula = UCase(Target.Formula) ErrHandler: Application.EnableEvents = True End Sub This code allows all columns up to column 8 (column H) to be affected What I would like to know is can I nominate a p...

Word 2007 Autotext and formatted text
I type the underlined text, select the text, hit Alt-F3, it's saved in Building Blocks, I name it and save it. When I go back to enter the text, either in the same document or in a new document days later, the text is NOT underlined. I have repeated the entry over and over. It never holds the underline. Does 2007 not hold formatting in autotext? Thanks for your help. How did you apply the underline formatting? Direct formatting (for example, applied by pressing Ctrl+U) should definitely be saved to the AutoText/Building Block entry. -- Stefan Blom Microsoft Word MVP ...

Need help. Conditional formatting problems
I have setup a worksheet that contains QC information. I created a simple "form" for the techs to enter there data into and then that "form" is repeated at least 52 (52 weeks in a year) times on the same worksheet. Setting up conditional formatting is very time consuming and I need a faster easier way to do it. This is what my "form" looks like: ------------------------------------------------------------------------------------------------ Week 1 Date set _*12/28/09_ Organism tested (E.coli) Initials __DA___ Drug tested ...

Numeric sign placement in custom format
I would like to format some cells with the text prefix "X = ". I've done this many times in the past with no problems, but apparently never had any negative numbers. The custom format string I am using is ["x = "0.000]. For positive numbers, it works fine. But for negative numbers, the "-" sign is placed before the "x": -x = 3.45 instead of before the number x = -3.45 Can someone help me fix this? "Jennifer Murphy" <JenMurphy@jm.invalid> wrote: > The custom format string I am using is ["x = "0.000]. > For ...

How do I format column labels?
A spreadsheet is showing the column headers in numbers. How can I change them back to letters? Tools | Options |General and clear R1C1 Reference Style "Shella" wrote: > A spreadsheet is showing the column headers in numbers. How can I change them > back to letters? ...

Date Format #17
I would like to convert a date that I've input from another program. When I input it I get 'Aug-28-2008. I want it to just be a regular date - so it would be *8/28/2008 if I were to select it in Format Cells. It says that I have General as the Category selected. When I switch it to Custom - nothing seems to happen. Any suggestions? First create a small table in the worksheet: Jan 1 Feb 2 Mar 3 Apr 4 May 5 Jun 6 Jul 7 Aug 8 Sep 9 Oct 10 Nov 11 Dec 12 and assign the name: nmths to the table. Then, with your data in A1, try: =DATE(RIGHT(A1,4),VLOOKUP(LEFT(A1,3),mnths,2,FALSE...

Cell Format 05-25-10
I want to enter <shift>: in a cell (this would be Shift Key and the colon character to enter a date). And then automatically format this cell so that the date is on one line, and the value "Critical Date" on the second line. So the cell value would look like this: 05/25/2010 Critical Date Can someone explain how to do this? If it can be done with code, I would be interested in that also... First, ctrl-: (control-colon) will enter the time, not the date. You want ctrl-; (control-semicolon) to enter the date. Second, you can use a custom number format to...

Apply Conditional Format to all sheets in same workbook
Is there a way to apply a conditional rule to all of the worksheets in a workbook? In other words, the cell range and format is the same for each sheet but I'd prefer not to create the rule for 40 pages. Thank you in advance. A CF is a format like any other format. Create one page and then copy and paste to the rest of the pages... -- HTH... Jim Thomlinson "Der Musensohn" wrote: > Is there a way to apply a conditional rule to all of the worksheets in a > workbook? In other words, the cell range and format is the same for each > sheet but I&...

Setting an Icon Set conditional format for a single cell???
This is my first question here and I'm pretty new to Excel, but I have a question about the Icon Set conditional formatting. From what I understand it's intended for use of a range of cells. However, I have a single cell that I want to display 1 of the 3 light icons for based on a certain condition. For example, this cell is a percentage. If the percentage is greater than 80%, display a green light. If the percentage is greater than or equal to 60% but less than or equal to 80%, display a yellow light. If the percentage is lower 60%, display a red light. Steps that I've done so ...

Date Format Doesn't Work
Vista Business, Office 2007. On one Vista of my three machines (2 Vista, 1 XP Pro SP2) date formatting does not work. I have tried a blank sheet. I have tried the other 2 machines they work both with blank sheets and with the a sheet saved on the problem machine. I have run repair without any improvement. Still the date will not format. With the Ctrl+; shortcut the date appears as set in regional settings. This has occurred (and started) within the last two weeks. (Sorry I don't recall exactly when the problem started. Any suggestions? TIA I'd try formatting the cell as Gene...

Conditional chart formating
I need to format my charts so the color of the data points change depending on the value amount. My tabels are already set up and cannot be change or have anything new added due to the amount and delicacy of data. Is there a macro I can use. Perhaps a formula I can put somewhere? Hi, Ed Ferrero has a vba example. It's for a column chart but the code will be very similar. Instead of interior fill you will be doing marker stuff. eg: .MarkerBackgroundColorIndex = 50 .MarkerForegroundColorIndex = 50 Conditional Chart Formatting example. http://www.edferrero.com/charti...

Having trouble formatting times
I've received a few good pointers, but haven't found the magic combination yet. I want to be able to enter times without using any seperators. I have the VBA code that does that, but the formats aren't coming out right. Here's what I'm getting: (number on left is raw input for cell, value on right is result) 1 - 00:01:00 12 - 00:12:00 123 - 01:23:00 1234 - 12:34:00 12345 - 01:23:45 123456 - 12:34:56 Here's what I want to get: 1 - 00:00:01 12 - 00:00:12 123 - 00:01:23 1234 - 00:12:34 12345 - 01:23:45 123456 - 12:34:56 How can I get that result? -- John Oliver, C...

Conditional Formatting
Using conditional formatting in a group of cells, I know how to make the highest number appear in bold type: =MAX($C$14:$C$23), then format BOLD. Is there a way to apply this condition to a chart? thanks Atom, It can be done via an event macro. If you're willing, we can give you some code. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "atom" <slkdjfs@asdfa.net> wrote in message news:3FB130AE.AF630C10@asdfa.net... > Using conditional formatting in a group of cells, I know how to make the > highest numb...

Conditional formats won't print
I am using Excel 2003 (11.6355.6360) SP1 I have a spreadsheet with cells conditionally formatted to turn green or red. Everything looks fine on the screen...but when I print, I don't see any of the colors..... The cells show up without any color formatting (ie they look just like any other non-conditionally formatted cell). Not sure what has changed b/c I've printed conditionally formatted spreadsheets in color in the past. Anyone have any ideas? "consumer" wrote: > I am using Excel 2003 (11.6355.6360) SP1 > > I have a spreadsheet with cells conditional...

troubleshooting date formats
I am having major problems with formatting dates. When I enter 10804 and try to format the cell (format,cells,number.date,03/14/98), the number in the cell changes to 07/30/29. When I type 1/08/04, it changes to 3-79-94. What is going on?? I tried going into tools, options, calculations, 1904 date system to see if the date program was off but when I tried that, it changed 01/08/04 into 1/9/04. I am at a complete loss and am going crazy. I am entering dates as text right now, but I need to use these dates in calculations. Hi basically, excel stores dates as a number, 1 being the 1...

change scientific format of numbers
Hello, I'm trying to publish a paper to a scientific journal including graphs generated with Excel XP. When a log scale is used the journal format requires the scientific format of the axis numbers to be like 10^n (10 superscript the exponent number) rather than 1E+n as it is standard in Excel. Is there an easy way to change that format? Thanks, Gigi There is no way to do this with axis number - one of the weakness of Excel for scientific use. I expect you could fake it by using labels. How many graphs do you have? -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail...

Excel 2007 Formatting loss
HI , I have a Excel file which is Saved as Macro Enabled (2007) with formattings ,it Contains Some input cells. when we updated those input cells and Save it after re-opening the excel file the some of the Formating are gone Can anyone help me on this Thank s in Advance What types of formatting are lost? Do you have any event code that may be doing the deed? Gord Dibben MS Excel MVP On Mon, 14 Dec 2009 14:24:03 -0800, gan49 <gan49@discussions.microsoft.com> wrote: >HI , >I have a Excel file which is Saved as Macro Enabled (2007) with formattings &...

Conditional addition of cells
I want to add the sales (column B) of the people in column A. A B James 23 Randy 43 Paul 12 James 40 Paul 50 ....and get this result: C D James 63 Randy 43 Paul 62 Only the result in column D needs to be calculated. Any help much appreciated. Since you state that: "Only the result in column D needs to be calculated" I'll assume that you have already entered the list of unique names in Column C. So, in D1 enter this: =Sumif(A$1:A$5,C1,B$1:B$5) And copy down. -- HTH, RD ---------------------------------...

Pivot Table vs Data Source Format
Excel 2000 ... Relatively new to Pivot Tables. Issue ... Source Data contains Date Field formatted(mm/dd/yy) ... I wish Pivot Table to calculate data by MONTH. I tried formatting Source Data to Date "Mar-98", but Pivot Table is still seeing the individual Days. Above said ... I know this is due to my short-comings in use of Pivot Tables so I have again come to the Magicians of this board for assistance ... Thanks ... Kha Ken, in your finished pivot table, click any date entry and Data > Group and Outline > Group. Select Months as the unit to group by, then OK. DDM &...

how to convert an AVI Y800 format movie to seria of bitmap 24 file
does anyboby knows.... what is the most simple c program to convert an AVI Y800 format movie to seria of bitmap 24 files? I know how to do it with BMP24bits (8,8,8): int main(int argc,char *argv[]) { LPBITMAPINFOHEADER pbmih1; PAVIFILE pavi1; PAVISTREAM pstm1; PGETFRAME pfrm1; LPBITMAPINFOHEADER pbmih2; PAVIFILE pavi2; PAVISTREAM pstm2; PGETFRAME pfrm2; AVIFileInit(); if (AVIFileOpen(&pavi1,FilePath1,OF_READ | OF_SHARE_DENY_NONE,NULL)!=0) { printf("Cant open file %s\n",FilePath1); return 0; } else { printf("op...

Convert variable #'s in a gen. format to a # that can be used w/ma
I have this data that is pasted to my worksheet: Period: 01/23/2010 to 01/29/2010 - Days: 6 It is in general format. The day numbers will always be in the last, last 2 , or last 3 positions. Depending on the period, the days may be any # up to the 365. I need to use that day #, be it 6, or 25, or 147, whatever, as a divisor of another number. E.g. if 6, then 1234 / 6 = 206 if 25, then 5678 / 25 = 227, etc/ The variable #'s will be in the same cell, so I basically want: E29/6 or E29/25, or E29/147, etc. Basically, those general formatted #'s converted to a usable/divisa...

Date formating for sorting
I have a genealogy data sheet and one column contains dates. I have formatted the column various different ways showing month/day/year, etc. If it is possible, I want to sort the column by year first, then month & day. Is that do-able? Thanks in advance. That's the way dates should be sorted. Maybe your data isn't really dates???? Or maybe you just need to specify ascending or descending???? - Butch wrote: > > I have a genealogy data sheet and one column contains dates. I have > formatted the column various different ways showing month/day/year, etc. &...