Conditional Formatting, a mind of its own, driving me nuts!

[lPinkFloyd]

I have a bizarre problem with my conditional formatting. First of all I have a sheet that has several time cards in it and the conditional formatting is working in all of them except one of them. The problem is occurring in

three of the cells. I am giving an example for only one set, but it is the same issue in the other two. The format conditions are as follows: Condition 1: If cell (xxx) value is = cell (yyy) cell color is none; Condition

2: If cell (xxx) value > cell (yyy) cell color is green; Condition 3: If cell (xxx) value < cell (yyy) cell color is red. The formulas that give me my value in cell (xxx) are correct. When I insert hypothetical data in the work

sheet to test the conditional formatting everything seems to be working, but when I enter the real data the conditional data starts acting up. For instance when cell (xxx) = (yyy) the color is red. There are three other

cells where this is happening.
I have deleted all data in these cells using delete contents, and redid the conditional formatting even going so far as to rearrange the order of the

conditions. I have double checked to make sure that I am referencing the correct cells. I did a copy from another section where the conditional formatting was working and changed the conditional formatting to make

sure I was referencing the correct cells. I tried using Format Painter and still am having the same problem. I deleted all the data and reentered it in the rest of the part of the sheet where the (xxx) gets its results from. It

seems like the main problem is with the less than part of the conditional formatting. I’ve looked around the forum but have not come across this issue. I have spent countless hours trying to make this work and as my last resort I am writing this. I know it is a long explanation for such what seems like a simple problem, but I am stuck. Using Windows XP and Office XP. Thanks

 

[Richie(UK)]

Re: Conditional Formatting, a mind of its own, driving me nu

Hi Pinky,

Are you sure that the values in the cells concerned are what you think they are?

Consider the following:

Book1.xls
	A	B	C	D
1	1	1	Notequal
Sheet1

On the face of it A1 and B1 are equal, so why does the formula in C1 tell us that they are not? Because A1 is actually 0.9 but it is formatted such that it appears as a 1.

Forgive me if I'm stating the obvious and you've already checked this but its often the simple things that get overlooked! :wink:

 

[lPinkFloyd]

Re: Conditional Formatting, a mind of its own, driving me nu

Richie, thanks for getting back to me on this. Yes i made sure that the values were correct. As a matter of fact I inserted the cell reference by selecting it and it is the correct reference and the amounts match. I even typed the information using the decmil point instead of letting the cell format take care of this. The thing that hase me all jacked up is that it works in the rest of the sheet. I am going to post the sheet as soon as i get a chance.

 

[Egress1]

Re: Conditional Formatting, a mind of its own, driving me nu

Howdy IPinkFloyd,

Have you tried selecting the cells that are working fine then doing a Copy - Edit - Paste Special - Formats?

So that as Richie says you are actually comparing apples to apples? Consider the following where both are actually 1. One is formatted as a number the other is formatted as text.

MasterXL.xls
	A	B	C	D
8	1	1	NotEqual
9
10
11
Sheet1

 

[lPinkFloyd]

Re: Conditional Formatting, a mind of its own, driving me nu

Yes I double checked to make sure the cells were formatted the same way and as I stated did a copy and format paste as well. I posted the sheet in the test forum but the Conditional format part of it didn't post. It is under the title "test conditional format."

 

[Egress1]

Re: Conditional Formatting, a mind of its own, driving me nu

Post it here and tell us Which Cells you have the Conditional Format applied to.

 

[GorD]

Re: Conditional Formatting, a mind of its own, driving me nu

Could you go to one of the cells with the error, click conditional format from the format menu and post the formulas that are shown.

 

[lPinkFloyd]

Re: Conditional Formatting, a mind of its own, driving me nu

Copy of Syl's Timecard.xls
	B	C	D	E	F	G	H	I	J	K	L
6		30-Nov-03	01-Dec-03	02-Dec-03	03-Dec-03	04-Dec-03	05-Dec-03	06-Dec-03	Wk.Totals	Store'sFigures
7	Shift1-ClockIn								Avg./Day	Total$Diff.?	$0.00
8	Shift1-ClockOut								0.00	TotalHr.Diff.?	0.00
9	Shift1Hrs.	0:00	0:00	0:00	0:00	0:00	0:00	0:00	$0.00	OT$Difference?	$0.00
10	Earnings	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	$0.00	?Reg.Pay?	$0.00
11	Shift2-ClockIn								$0.00	?OTPay?	$0.00
12	Shift2-ClockOut								$0.00	?TotalPay?	$0.00
13	Shift2Hrs.	0:00	0:00	0:00	0:00	0:00	0:00	0:00	0.00	?OTHrs.?
14	TotalHours	0:00	0:00	0:00	0:00	0:00	0:00	0:00	0.00	?Total/Reg.Hrs?	0.00
Sheet2

 

[lPinkFloyd]

Re: Conditional Formatting, a mind of its own, driving me nu

L13 is suppose to stay white when = to J13 and change to red when< L13, And change to Green when> L13; L7 is to stay white when =0, Turn Green when >0, Turn Red when<0; L9 to stay white when =0, Turn Green when >0, Turn Red < 0. The other cells in column L are similar and are working. Cell L14 is Yellow until an amount is entered then turns to the same color as row 13. all of that is working fine. Go figure.

 

[parry]

Re: Conditional Formatting, a mind of its own, driving me nu

A pity there is no option to download the workbook as it would be easier to be able to play around with it to see where the problem is.

Things to check...
1) Worksheet_Change events
2) Hard formatting of colours rather than conditional formatting. Conditional formatting takes precendence to hard formatting so if no condition is met then the appearance may be a colour which was hard formatted so you may think conditional formatting is doing this when its not.
3) The cell in question has multiple or different conditions to others. (see below)
4) Delete the cell and see what happens in a new cell.

You can check whether cells have the same formatting by doing this...
1) Click in the cell in question
2) Select Edit|Goto|Special then select the Conditional Formats option and the Same option rather than All.
This will select all cells in the sheet that have the same formatting. If no other cells are selected, then this shows that the conditional formatting is different in some way.


If its not one of these then Im out of ideas.

hth