*Official* Excel help thread • Non-Gaming • Software • Eurogamer.net

catterz 1 Jun 2007 09:48:12 8,763 posts
Seen 8 years ago
Registered 19 years ago

I'm trying to format a cell to change colour when I edit the data but can't figure it out.

I've tried Conditional Formating, but I don't know the command (if there is one).

Anyone know?

Carlo 1 Jun 2007 09:49:37 21,801 posts
Seen 2 days ago
Registered 16 years ago

I WILL HELP YOU

catterz 1 Jun 2007 09:50:04 8,763 posts
Seen 8 years ago
Registered 19 years ago

Yay \o/

So...?

Lutz 1 Jun 2007 09:50:28 48,870 posts
Seen 4 years ago
Registered 18 years ago

How do you mean "edit the data"?

Coniditional formatting is effectivly a formula for colours etc.

So you'd have something like:

=if(A1>10,"Red","Blue)

(That's not what you'd write BTW)

That would mean is A1 had a value of 10+ then the background would be red. If not, it'd be blue.

pauleyc 1 Jun 2007 09:51:14 4,548 posts
Seen 22 hours ago
Registered 20 years ago

Conditional formatting is under the Format menu; not really any commands associated with it, just up to 3 conditions depending on the value of the current cell (works also when a range is selected).

edit: Formulas are possible but the simple part works just as well with conditions entered from drop lists. Or what Lutz said.

Edited by pauleyc at 09:53:11 01-06-2007

catterz 1 Jun 2007 09:51:55 8,763 posts
Seen 8 years ago
Registered 19 years ago

I mean any change in the data at all. Is there a condition in Excel formulas to ask whether a cell is no longer equal to itself (or it's old value)?

I need something along the lines of:

=IF(A1 NOT EQUAL TO A1,Yellow,)

Edited by catterz at 09:52:56 01-06-2007

catterz 1 Jun 2007 09:53:59 8,763 posts
Seen 8 years ago
Registered 19 years ago

The values are text so most formulas won't apply. Should've mentioned that before.

Lutz 1 Jun 2007 09:55:35 48,870 posts
Seen 4 years ago
Registered 18 years ago

Hmm... You COULD sorta do it.. I think...

OK... if you have Column A as your data OK?

In B1 you want the conditional formatting to read:

=if(B1=A1,"Normal","RED")

In B1 have:

=A1

and run it down. Then copy column B, paste special, click values.

Then if A changes, the B cell next it will change red.

I don't have excel on this PC, otherwise I'd give you a proper solution. But that should give you an idea to be able to hammer out yourself.

Edited by Lutz at 09:56:42 01-06-2007

Lutz 1 Jun 2007 09:56:14 48,870 posts
Seen 4 years ago
Registered 18 years ago

catterz wrote:
The values are text so most formulas won't apply. Should've mentioned that before.

Yeah, the formulas are inside the coditional formatting itself. Don't worry about that.

pauleyc 1 Jun 2007 09:57:29 4,548 posts
Seen 22 hours ago
Registered 20 years ago

I think you might be looking for "Change tracking" in shared workbooks.

On the Tools menu, click Share Workbook, and then click the Editing tab.
Select the Allow changes by more than one user at the same time check box.
Click the Advanced tab.
Under Track changes, click Keep change history for, and in the Days box, type the number of days of change history that you want to keep.
Be sure to enter a large-enough number of days because Microsoft Excel permanently erases any change history older than this number of days.
Click OK, and if prompted to save the file, click OK.

Edited by pauleyc at 09:58:58 01-06-2007

catterz 1 Jun 2007 09:57:47 8,763 posts
Seen 8 years ago
Registered 19 years ago

I just had that idea myself. I guess I'll do that for now then.

Cheers.

ED209 1 Jun 2007 09:57:50 505 posts
Seen 4 years ago
Registered 16 years ago

You can't check that with conditional formatting. You'd have to copy the column at a point in time and then compare the values in the original column with the values in the copied column to see if they have changed.

ie. what Lutz said.

Edited by ED209 at 09:59:08 01-06-2007

Lutz 1 Jun 2007 09:58:29 48,870 posts
Seen 4 years ago
Registered 18 years ago

ED209 wrote:
You can't check that with conditional formatting. You'd have to copy the column at a point in time and then compare the values in the original column with the values in the copied column to see if they have changed.

You mean my post above?

catterz 1 Jun 2007 09:58:42 8,763 posts
Seen 8 years ago
Registered 19 years ago

pauleyc wrote:
I think you might be looking for "Change tracking" in shared workbooks.

As far as I read this wouldn't work as needed.

catterz 1 Jun 2007 09:58:53 8,763 posts
Seen 8 years ago
Registered 19 years ago

Lutz wrote:

ED209 wrote:
You can't check that with conditional formatting. You'd have to copy the column at a point in time and then compare the values in the original column with the values in the copied column to see if they have changed.
You mean my post above?

Yea, he does

Lutz 1 Jun 2007 10:00:57 48,870 posts
Seen 4 years ago
Registered 18 years ago

That thing above would work for a one off. It's a bit cumbersome if you need to do it all the time though.

If you need a proper job then wait for one of the true Excel gurus to pop up. Not that Iv'e seen them for a while mind.

reality_cheque 1 Jun 2007 10:23:11 7,486 posts
Seen 2 years ago
Registered 16 years ago

It could be done with VBA, on a change_value trigger.

Don't ask me for the code though! This page has some stuff on colours in VBA, was very handy when I had to change values based on colours...

reality_cheque 1 Jun 2007 10:25:42 7,486 posts
Seen 2 years ago
Registered 16 years ago

Lutz wrote:
If you need a proper job then wait for one of the true Excel gurus to pop up. Not that Iv'e seen them for a while mind.

They're probably all hiding

I've been doing odd things with excel for years, and this would be one of the most pain-in-the-arse things I've ever seen someone try and do!

catterz 1 Jun 2007 10:28:47 8,763 posts
Seen 8 years ago
Registered 19 years ago

I'm quite proficient with VB, but I'd probably spend more time doing the coding than actually using the resultant auto-formating cells for what they're needed for.

It's just a small job, but apparently there's no quick fix. Manual formatting ftl.

reality_cheque 1 Jun 2007 10:35:29 7,486 posts
Seen 2 years ago
Registered 16 years ago

Out of curiosity, what would trigger the cell to revert to it's old colour? Anything? How are you detecting if a value gets changed twice?

catterz 1 Jun 2007 11:15:51 8,763 posts
Seen 8 years ago
Registered 19 years ago

It was only the one off use. I wouldn't need a cell to revert. Just a comparison between the new and old versions where I could highlight changes I made myself without having to faff around formating each cell.

avtar 1 Jun 2007 11:30:16 94 posts
Seen 1 year ago
Registered 16 years ago

If you only need to compare the new values with an original set of values you can make a copy of the sheet, then the use simple conditional like does not equal and compare to the cell in the copied sheet.

If you remove the absolute references (the "$"'s ) you can use the format painter to copy the conditional over to the whole sheet and the compared to cell will adjust like it will when you fill down.

gang_of_bitches 1 Jun 2007 11:32:33 5,707 posts
Seen 2 years ago
Registered 16 years ago

HIJACK ALERT:

On a similar note I've been trying manage a chart and highlight a row based on whether a title has moved up or down in position. Its easy enough to generate a colour just by having 2 columns with This Week and Last week and a greater than or less than format. However this only highlights 1 cell, is there a way I can make it highlight a range of cells (ie artis, title etc.)? You'd think it was obvious but I can't work it out.

avtar 1 Jun 2007 11:45:55 94 posts
Seen 1 year ago
Registered 16 years ago

This page should show you have to do a chart which has different colours in it. You can change to formulas to test for artist, title etc

Edited by avtar at 11:46:19 01-06-2007

gang_of_bitches 1 Jun 2007 11:59:15 5,707 posts
Seen 2 years ago
Registered 16 years ago

avtar wrote:
This page should show you have to do a chart which has different colours in it. You can change to formulas to test for artist, title etc

Edited by avtar at 11:46:19 01-06-2007

Thanks for the effort avtar, but I'm afraid I didn't make myself entirely clear.

I was talking about a games chart for a popular retailer. So I've got various charts and for the convenience of others I need to highlight lines that are new entries, moving down and moving up (why they can't just look at the numbers I don't know). So basically each row has: Last week's position, This week's position, Title, Price, etc, etc. Now its a doddle to just highlight the This week's position cell with a colour to reflect the move, but I want to extend that to also highlight the Title, Price, etc. cells. Any ideas?

avtar 1 Jun 2007 12:07:40 94 posts
Seen 1 year ago
Registered 16 years ago

Stupid question, is it a chart or a table?

gang_of_bitches 1 Jun 2007 12:59:40 5,707 posts
Seen 2 years ago
Registered 16 years ago

avtar wrote:
Stupid question, is it a chart or a table?

Its just a simple spreadsheet with data manually entered into cells. So a table I guess.

reality_cheque 1 Jun 2007 13:01:56 7,486 posts
Seen 2 years ago
Registered 16 years ago

Just select all of those cells you want to change before bringing up the conditional formatting box, but I think you might have to do it a row at a time.

The other way round is make a 'difference' column you have hidden, and base it on that.

For example A = old position, B = new position, C = A - B

C < 0 : red (dropped position)
C = 0 : yellow (non mover)
C > 0 : green (gained position)

Edited by reality_cheque at 13:04:45 01-06-2007

gang_of_bitches 1 Jun 2007 13:10:15 5,707 posts
Seen 2 years ago
Registered 16 years ago

reality_cheque wrote:
Just select all of those cells you want to change before bringing up the conditional formatting box, but I think you might have to do it a row at a time.

The other way round is make a 'difference' column you have hidden, and base it on that.

For example A = old position, B = new position, C = A - B

C < 0 : red (dropped position)
C = 0 : yellow (non mover)
C > 0 : green (gained position)

Edited by reality_cheque at 13:04:45 01-06-2007

Doesn't like it. As its the formatting that is conditional it only seems to want to work with the cell that is actually being compared. That is it will work in the cell which is greater or less than the previous one, but when it comes to the text cell that no longer applies so it doesn't work. You'd think there'd be a way of saying put whatever colour appear in x1 into y1 and z1, but if there is I don't know it.

Grunk 1 Jun 2007 13:17:40 4,718 posts
Seen 2 weeks ago
Registered 17 years ago

Do you want the text cell that you've entered data into to change colour?

If so I don't think you can do this by a formula alone; i.e. if you enter a formula then the result of a formula only affects the cell the formula is in, which means if you then enter data into that cell you will overwrite the formula.

However, you can create a macro to do it, probably the easiest way to get the colour changing code is to record a macro and change the colour in one of the cells, then write a VB loop around it. Shouldn't take too long.

Alernatively, you can have one input worksheet and one output worksheet, the input work sheet holds the data, name, chart postition etc. from this week and last week. The output worksheet, contains only formulas which change the cell colour and then you can use the offset function to place the title in the appropriate chart position.