Formula to set cell color

I have a column containing a sorted list of values which includes duplicate 
values such as:

Row 1:  1
Row 2:  1
Row 3:  2
Row 4:  3
Row 5:  3
Row 6:  4
Row 7:  4
Row 8:  4
Row 9:  5

Without using VBA or macros (an unfortunately non-negotiable requirement of 
the customer) I'm tasked to do the following using conditional formatting:
1. Make a cell that has the same value as the cell immediately above it the 
same color as that of the cell immediately above it
2. Make the color of a cell whose value is not the same as the cell 
immediately above it a different color than the cell immediately above it
3. Alternating two colors is fine

For my example data, the cells in rows 1 and 2 should be yellow, row 3 
should be blue, row 4 and 5 should be yellow, row 6 through 8 should be 
blue, and row 9 should be yellow.

Using Excel 2003 and just formulas, I don't think that this is possible but 
I'm hoping that someone out there will know better. I don't know if Excel 
2007 has added something to make this possible.

Anyone know of a way to do this?

Brian 


0
BrianB
4/23/2010 5:17:24 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
679 Views

Similar Articles

[PageSpeed] 47

I'm going to step out on a limb and say I don't think it can be done with 
just conditional formatting either.  The conditional format doesn't know what 
color the previous cells are.  I can set up conditional format with formulas 
that will change colors of cells with contiguous groups, but not in 
alternating fashion.  In your series of data, assuming we default shade 
things to Yellow and switch to blue when we have a contiguous group, then:

Row 1:  1 - changed to blue
Row 2:  1 - changed to blue
Row 3:  2 - no change, YELLOW
Row 4:  3 - changed to blue
Row 5:  3 - changed to blue
Row 6:  4 - changed to blue
Row 7:  4 - changed to blue
Row 8:  4 - changed to blue
Row 9:  5 - no change, YELLOW

and I fully realize that's not what you want.  You can come close by setting 
up formulas that will change the colors of all but the 1st of a series.  
Choose row 2 to the end of the list, and in Conditional Formatting

Cell Value Is     "equal to"    A1
and set the alternative format.  That gives us:
Row 1:  1 - no change, YELLOW
Row 2:  1 - changed to blue
Row 3:  2 - no change, YELLOW
Row 4:  3  - no change, YELLOW
Row 5:  3 - changed to blue
Row 6:  4 - no change, YELLOW
Row 7:  4 - changed to blue
Row 8:  4 - changed to blue
Row 9:  5 - no change, YELLOW



"BrianB" wrote:

> I have a column containing a sorted list of values which includes duplicate 
> values such as:
> 
> Row 1:  1
> Row 2:  1
> Row 3:  2
> Row 4:  3
> Row 5:  3
> Row 6:  4
> Row 7:  4
> Row 8:  4
> Row 9:  5
> 
> Without using VBA or macros (an unfortunately non-negotiable requirement of 
> the customer) I'm tasked to do the following using conditional formatting:
> 1. Make a cell that has the same value as the cell immediately above it the 
> same color as that of the cell immediately above it
> 2. Make the color of a cell whose value is not the same as the cell 
> immediately above it a different color than the cell immediately above it
> 3. Alternating two colors is fine
> 
> For my example data, the cells in rows 1 and 2 should be yellow, row 3 
> should be blue, row 4 and 5 should be yellow, row 6 through 8 should be 
> blue, and row 9 should be yellow.
> 
> Using Excel 2003 and just formulas, I don't think that this is possible but 
> I'm hoping that someone out there will know better. I don't know if Excel 
> 2007 has added something to make this possible.
> 
> Anyone know of a way to do this?
> 
> Brian 
> 
> 
> .
> 
0
Utf
4/23/2010 6:36:02 PM
On Apr 23, 1:36=A0pm, JLatham <JLat...@discussions.microsoft.com> wrote:
> I'm going to step out on a limb and say I don't think it can be done with
> just conditional formatting either. =A0The conditional format doesn't kno=
w what
> color the previous cells are. =A0I can set up conditional format with for=
mulas
> that will change colors of cells with contiguous groups, but not in
> alternating fashion. =A0In your series of data, assuming we default shade
> things to Yellow and switch to blue when we have a contiguous group, then=
:
>
> Row 1: =A01 - changed to blue
> Row 2: =A01 - changed to blue
> Row 3: =A02 - no change, YELLOW
> Row 4: =A03 - changed to blue
> Row 5: =A03 - changed to blue
> Row 6: =A04 - changed to blue
> Row 7: =A04 - changed to blue
> Row 8: =A04 - changed to blue
> Row 9: =A05 - no change, YELLOW
>
> and I fully realize that's not what you want. =A0You can come close by se=
tting
> up formulas that will change the colors of all but the 1st of a series. =
=A0
> Choose row 2 to the end of the list, and in Conditional Formatting
>
> Cell Value Is =A0 =A0 "equal to" =A0 =A0A1
> and set the alternative format. =A0That gives us:
> Row 1: =A01 - no change, YELLOW
> Row 2: =A01 - changed to blue
> Row 3: =A02 - no change, YELLOW
> Row 4: =A03 =A0- no change, YELLOW
> Row 5: =A03 - changed to blue
> Row 6: =A04 - no change, YELLOW
> Row 7: =A04 - changed to blue
> Row 8: =A04 - changed to blue
> Row 9: =A05 - no change, YELLOW
>
> "BrianB" wrote:
> > I have a column containing a sorted list of values which includes dupli=
cate
> > values such as:
>
> > Row 1: =A01
> > Row 2: =A01
> > Row 3: =A02
> > Row 4: =A03
> > Row 5: =A03
> > Row 6: =A04
> > Row 7: =A04
> > Row 8: =A04
> > Row 9: =A05
>
> > Without using VBA or macros (an unfortunately non-negotiable requiremen=
t of
> > the customer) I'm tasked to do the following using conditional formatti=
ng:
> > 1. Make a cell that has the same value as the cell immediately above it=
 the
> > same color as that of the cell immediately above it
> > 2. Make the color of a cell whose value is not the same as the cell
> > immediately above it a different color than the cell immediately above =
it
> > 3. Alternating two colors is fine
Assuming you are in A1 to A9

Set B1 value to "Yellow", in B2 use :
=3DIF(A2=3DA1,IF(B1=3D"Yellow","Yellow","Blue"),IF(B1=3D"Yellow","Blue","Ye=
llow"))
Extend this down to B9.

Conditional format A1:AX with two rules:
1: formula =3Dif(b1=3D"Yellow",1,0)  --> format with yellow fill
2: formula =3Dif(b2=3D"Blue",1,0) --> format with blue fill

You would have to hide column B (or any column on the same or another
perhaps hidden page could be used)

Not the most elegant method, but it works.


TK

>
> > For my example data, the cells in rows 1 and 2 should be yellow, row 3
> > should be blue, row 4 and 5 should be yellow, row 6 through 8 should be
> > blue, and row 9 should be yellow.
>
> > Using Excel 2003 and just formulas, I don't think that this is possible=
 but
> > I'm hoping that someone out there will know better. I don't know if Exc=
el
> > 2007 has added something to make this possible.
>
> > Anyone know of a way to do this?
>
> > Brian
>
> > .

0
Cortez
4/23/2010 8:41:05 PM
"Cortez" <tdk1138@gmail.com> wrote in message 
news:53601985-8bdc-4aba-b961-d231cfbbb672@j21g2000yqh.googlegroups.com...
On Apr 23, 1:36 pm, JLatham <JLat...@discussions.microsoft.com> wrote:
> I'm going to step out on a limb and say I don't think it can be done with
> just conditional formatting either. The conditional format doesn't know 
> what
> color the previous cells are. I can set up conditional format with 
> formulas
> that will change colors of cells with contiguous groups, but not in
> alternating fashion. In your series of data, assuming we default shade
> things to Yellow and switch to blue when we have a contiguous group, then:
>
> Row 1: 1 - changed to blue
> Row 2: 1 - changed to blue
> Row 3: 2 - no change, YELLOW
> Row 4: 3 - changed to blue
> Row 5: 3 - changed to blue
> Row 6: 4 - changed to blue
> Row 7: 4 - changed to blue
> Row 8: 4 - changed to blue
> Row 9: 5 - no change, YELLOW
>
> and I fully realize that's not what you want. You can come close by 
> setting
> up formulas that will change the colors of all but the 1st of a series.
> Choose row 2 to the end of the list, and in Conditional Formatting
>
> Cell Value Is "equal to" A1
> and set the alternative format. That gives us:
> Row 1: 1 - no change, YELLOW
> Row 2: 1 - changed to blue
> Row 3: 2 - no change, YELLOW
> Row 4: 3 - no change, YELLOW
> Row 5: 3 - changed to blue
> Row 6: 4 - no change, YELLOW
> Row 7: 4 - changed to blue
> Row 8: 4 - changed to blue
> Row 9: 5 - no change, YELLOW
>
> "BrianB" wrote:
> > I have a column containing a sorted list of values which includes 
> > duplicate
> > values such as:
>
> > Row 1: 1
> > Row 2: 1
> > Row 3: 2
> > Row 4: 3
> > Row 5: 3
> > Row 6: 4
> > Row 7: 4
> > Row 8: 4
> > Row 9: 5
>
> > Without using VBA or macros (an unfortunately non-negotiable requirement 
> > of
> > the customer) I'm tasked to do the following using conditional 
> > formatting:
> > 1. Make a cell that has the same value as the cell immediately above it 
> > the
> > same color as that of the cell immediately above it
> > 2. Make the color of a cell whose value is not the same as the cell
> > immediately above it a different color than the cell immediately above 
> > it
> > 3. Alternating two colors is fine
Assuming you are in A1 to A9

Set B1 value to "Yellow", in B2 use :
=IF(A2=A1,IF(B1="Yellow","Yellow","Blue"),IF(B1="Yellow","Blue","Yellow"))
Extend this down to B9.

Conditional format A1:AX with two rules:
1: formula =if(b1="Yellow",1,0)  --> format with yellow fill
2: formula =if(b2="Blue",1,0) --> format with blue fill

You would have to hide column B (or any column on the same or another
perhaps hidden page could be used)

Not the most elegant method, but it works.


TK

Looks good, TK. I'll give it a try.

Brian 


0
BrianB
4/29/2010 9:37:07 PM
Reply:

Similar Artilces:

Excel VB-Copy formula down until adjacent cell (left) is blank?
Here is exactly what I am trying to do through VB in Excel: Weekly data pull fills colums A:G. Row count is always different. I am modifying the data pull through VB, and I have a VLOOKUP formula in cell H2. What I want VB to do is copy that formula down column H to the last row (with data) each week. I guess I want it to be dynamic so that as rows decrease/increase the formula is only copied down to the final row/record. I know someone out of this smart group will know how to do this! Thanks in advance! Tony (pseudo code) in a macro .... dim lngLastrow as long dim rngTarget...

Can't set Date Properties.
I am working with data that has dates set as text. So in a query I am using a variable that sets the text to a date suing CDATE. At least I thought it did. I notice that in forms etc when I try to set the date property to long, medium etc, there is nothing to choose so presumably it is not a date after all! Any idea what I'm doing wrong please. Cheers. Roger --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.673 / Virus Database: 435 - Release Date: 01/05/2004 Sorted. I hadn't set the function as a date. Well I am n...

Summing sets of numbers but with special conditions
Hi - I need a formula to do the following. I have 2 columns of six numbers - for example: A B 1 75 70 2 83 69 3 80 72 4 84 67 5 95 83 6 93 80 The formula needs to do the following: 1. Find the lowest number in column A (75 in row 1 in the above example) 2. Discard the number in corresponding cell in column B (70 in cell B1) 3. From the remaining numbers in column B, discard the highest (83 in cell B5) 4. Add together the remaining 4 numbers in column B (69, 72, 67, 80) and add this result to the number found in step 1 (75) to give a total (363) Any ideas/pointers most w...

Need Help with Formula #3
I need help trying to come up with a formula for a friend of mine. This is what he wants -- Using any 9 numbers -- he wants Excel to come up with every possible 3-digit combination of numbers that are divisible by 7. Is there anyone who could assist me with a formula that will perform this calculation? Can Excel do such a calculation? Thanks so much for any assistance you can offer. In A1, enter the number 7. Select A1:A142. Edit>Fill>Series, Step value 7. Format>Cells>Custom, "000". If with "using any 9 numbers" you mean you don't want any zero...

Q: Referencing named cells in external worksheet ?
Using Excel 2002. I have a workbook with 12 worksheets (one for every month of the year), wherein a lot of the information is looked-up (using VLOOKUP) in simples arrays. I saw no point in implementing the arrays as a 13th worksheet, because I will have a yearly version of my monthly worksheets in one workbook (so one for 2003, 2004, etc). If I change the array(s), I want them to be reflected in all referencing cells. Problem: If my workbook containing my arrays (called "Global") is loaded, I have no problem and the references to it read as: (blabla) 'Global.xls'!Roster ...

Visible cell characters
Can I increase the # of characters that are visible in a cell? 67 Excel Help on "limits" or "specifications" reveals that Excel will allow 32,767 characters to be entered in a cell. However, it goes on to state that "only 1024 characters will be visible or can be printed" To work around this limitation, stick a few ALT + ENTERs in at appropriate spots. The ALT + ENTER forces a line-feed and expands the 1024 limit. How far is not really known. Just experiment. .........From Dave Peterson.......... I put this formula in A1: ="xxx"& REPT(REPT(&...

Color Scheme Registry Key
Hello, I have a new computer that has Windows Vista Business Edition and had Office Standard Edition 2007 installed. I have since installed Office Small Business Editon 2007 on it. When I run Publisher and click on Color Scheme, I get the following message: "The color scheme registry key is either missing or corruput. Run Setup again to reinstall the color scheme registry." I have since reinstalled Office as directed, but with no success. I then uninstalled Office and reinstalled, again without success. Please help. Thanks, Zevon Hello Zevon, I have looked everywhere fo...

Cell Reference Problem with Network
Let's say I have a spreadsheet with two worksheets = SheetA and SheetB. SheetA might reference a cell in SheetB with a formula like =SheetB!A1 But when I move this to the network the reference changes to include the network drive and file name like: ='Z:\FOLDER\[FILE]SheetB!A1 the file may move from my laptop to the network several times and this becomes completely confusion as the reference looks, not within the same spreadsheet which is what I want it to to, but for another file out on the network. How do I explicitly reference a cell within a difference worksheet but alwa...

How to generate a truly empty cell
"" generates a zero-length string, not a truly empty cell. This is causing problems elsewhere. I'd like to find an output for an IF statement that will give me a truly empty cell. The current formula is: =IF(COUNT(C24:C29)>0,SUM(C24:C29),"") Any ideas? If it involves a macro (as I think it might, having read other posts), please explain how to implement it. Thanks! <This is causing problems elsewhere> It shouldn't. Don't use ISBLANK(A1), use A1="" -- Kind regards, Niek Otten Microsoft MVP - Excel "paulkaye" <paulmjkaye@gm...

Splitting cells
in cell A1 i have the numbers 123456789. i want cell B1 to have numbers 1234567 and cell C1 to have just 89. what is the formula? i have tried text to columns If this is for extracting the first 7 characters use LEFT() =LEFT(A1,7) =RIGHT(A1,2) -- Jacob (MVP - Excel) "fazz" wrote: > in cell A1 i have the numbers 123456789. i want cell B1 to have numbers > 1234567 and cell C1 to have just 89. what is the formula? i have tried text > to columns Hello Jacob - i did not explain this very well.The digits in cell A1 is variable length. In cell C1 i n...

Change background color of query results from access in worksheet
Hi, I have Excel 2007 and selected Date => From Access and then navigated to an Access DB and selected a query. I chose "Table" view. The query results now appear in my worksheet which is great. However, I want to change the background. Right now it alternatives by row between a light blue and a darker blue. I want the results to have a white background with black borders but have been unable to change the format. If I go to Home and select No fill for a color background nothing happens. Is there a way to change the query results? Thanks, Could be the r...

I need to write this formula in basic
Hi! Does anyone know how to write this formula in Basic. I need to make it work in Openoffice because Excel seems to crash with a list of 20000 people. =IF(ISERROR(VLOOKUP((RC1&R1C),Sheet1!R2C1:R45000C4,4,0)),"""",VLOOKUP ((RC1&R1C),Sheet1!R2C1:R45000C4,4,0)) Thanks!! ...

Non-VBA formula to find 2nd Sunday of a given month
Can anyone help me write a formula to find the date of the second sunda in a given month? Thanks in Advance, Dav ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com David, Assuming you have a date in A1, this gives the 2nd Sunday of that date =DATE(YEAR(A1),MONTH(A1),1)+MOD(8-WEEKDAY(DATE(YEAR(A1),MONTH(A1),1)),7)+7 -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "DavidObeid&q...

Functions & Formulas
Hello, I like to see if anyone is able to help me come up with a formula or function that will do the following for me: I have a set of 9 computer generated (somewhat random) numbers. THESE NUMBERS WILL NOT BE IN AN ASSENDING OR A DECENDING ORDER. Example is something like the following set of numbers: 18.34, 19.37, 20.4, 19.38, 17.96, and so on, up to nine numbers. I CAN ORDER THESE NUMBERS IN A ROW OR A COLUMN, EACH IN A SEPARATE CELL. HOWEVER, THIS IS HALF OF THE PROBLEM!. NOW I have another number, I WILL CALL THIS NUMBER MY CONTROL NUMBER. This control number (19.11...

how to set up a worksheet for unlimited entries?
I need to set up an excel worksheet where the data in the "Cost" colum is added up to show "Total Cost" at the bottom of the column. I can d this very easily for a static range but the "cost" column is no static, it will keep on growing. How do I write a formula for "Tota Cost" which will be aware of how many entries exist in the column s they can be added up. Also, I was going to have "total cost" cell a the bottom of the "cost" column, can "Total Cost" be set up in such way, that the "Total Cost" cell moves dow...

Error 1004
I am creating a macro enabled spreadsheet using Excel 2003. It reads data in from a .csv file and manipulates this data to create several reports. Some of the report users use Excel 97, and get the run time error 'Error 1004 - Unable to set specialcells property' when they try to run the macro that updates the reports from the .csv. I am using the special cells property to find the range that I need to clear when reading in new data. Does anyone know what might be causing this problem? Thanks in advance Hi post the relevant part of your code "Darach" wrote: >...

Setting multiple meeting times for different attendees on the same day
I am a recruiter and use Outlook to schedule interviews with out of town candidates. It's critical that all my interviewers be available on the same date but at different times during the day. I would like to be able to pull up all of my interviewers' schedules at the same time and schedule them in different time slots but only having to send one meeting request. Example: I have 5 managers that will be interviewing the same person on the same day at different times. I have to list them all as "required attendees", check their schedules, then back out and send o...

making a sum outcome negative based on adjacent cell value
I have 3 columns of single figures. At present i'm using the sumproduc fuction to multiply and total the figures in column A that fall betwee 4 and 9 with the adjacent figure in column B... =SUMPRODUCT(--($A1:$A600>=4),--($A1:$A600<=9),($B1:$B600),($A1:$A600) I'd like to add column C to the formula, so that if it contained value of -1, 1 or 2, the sum of the adjacent figures in columns A and appears as a negative number. For example A3= 7, B3= 2, C3= 1 Outcome= -14 A4= 9, B4= 1, C4= 5 Outcome= 9 A5= 3, B5= 2, C5= 2 No sum because figure in column A ...

Help with formula containing text
Hi I need some help on the following. I have a column of text, linked to other worksheets, that is continuously changing. I need to be alert if the same piece of text appears in the column more than twice, e.g. Tom Tom Jane Mary Mary Tom "ALERT" Thanks Mike -- mlhynes ------------------------------------------------------------------------ mlhynes's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=12959 View this thread: http://www.excelforum.com/showthread.php?threadid=401787 Mike Visit Chip Pearson's site for much help on duplicates. Fin...

Merged Cells
I have imported data into Excel. The left-hand column has merged cells containing a reference number. The remaining columns contain varying records associated with the reference number, a one to many ratio. I need to display the worksheet so that the appropriate reference number is displayed in the left-hand column for each of the records in the worksheet. There are hundreds of reference numbers. Is there an automated way to do this besides unmerging each section and copying the reference number into the now unmerged cells? Thank you. ...

HOW to COUNT THE FREQUENCY of specific CHARACTER WITHIN a CELL?
How can a frequency of a specific character be counted with in a cell. Ex -" #4 Bluebirds, #6 Aquatic" is in cell B2 - how do I count the number of "#" that appear in cell B2? =LEN(B2)-LEN(SUBSTITUTE(B2,"#","")) HTH Jason Atlanta, GA >-----Original Message----- >How can a frequency of a specific character be counted with in a cell. Ex -" >#4 Bluebirds, #6 Aquatic" is in cell B2 - how do I count the number of "#" >that appear in cell B2? >. > ...

VBA Range Formula
Can anybody tell me how to get this to work? In other words, how do I reference my procedure variables in a cell formula? Thanks! Sub Marktest() Set b = Cells.Find("Total", , xlValues) Set c = Range("C5") Range(b.Offset(-1, 2), c).Formula = _ "=sumproduct(($B5=Range(b.Offset(-7, 1), b.Offset(-5, 1)))*$C$1:$C$3)" End Sub I'd do something like: Option Explicit Sub Marktest2() Dim myRng As Range Dim b As Range Dim c As Range With ActiveSheet Set b = .Cells.Find("Total", , xlValues) Set...

Page set up
Is there any way to repeat a row at the BOTTOM of every page (Opposite to repeat row at top). No. This feature is not in the program specifications of Microsoft Excel. ----- Ranjit wrote: ----- Is there any way to repeat a row at the BOTTOM of every page (Opposite to repeat row at top). ...

Data Validation, Via A Formula
Is there a way to apply data validation to one cell, based on the result of another cell? For instance, if in A1, a data validation list displays sports (ie baseball, basketball,etc), and if the user selects "Baseball", then the data validation in B1 would only list baseball teams, from the range named baseball teams. And if basketball is selected, then only basketball teams are displayed. If at all possible, could this be done via a formula and WITHOUT using VB? If not, I could also use any help to accomplish this via VB coding. Any and all help will be appreciated...

How many decimal places can a cell display?
How many decimal places can be displayed in a cell? I'm running a brute force VBA procedure of finding fractions that will approximate pi to as many decimal places as Excel will display, but I don't know how many decimal places Excel will display accurately. Anybody know? I guess this is also a matter of how many decimal places VBA will calculate accurately as well. Sub PiFractions() Dim dividend As Integer, divisor As Integer, quotient As Double Dim rowpointer As Byte rowpointer = 1 For dividend = 22 To 10000 For divisor = 7 To dividend \ 3 quotient = dividend / diviso...