*Official* Excel help thread

  • Page

    of 10 First / Last

    Previous
  • catterz 1 Jun 2007 09:48:12 8,763 posts
    Seen 9 months ago
    Registered 12 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?

    Still stalking this place, old-school style (UID: 1053)

  • Carlo 1 Jun 2007 09:49:37 17,981 posts
    Seen 7 hours ago
    Registered 9 years ago
    I WILL HELP YOU

    PSN ID: Djini

  • catterz 1 Jun 2007 09:50:04 8,763 posts
    Seen 9 months ago
    Registered 12 years ago
    Yay \o/

    So...? ;)

    Still stalking this place, old-school style (UID: 1053)

  • Lutz 1 Jun 2007 09:50:28 48,854 posts
    Seen 1 year ago
    Registered 11 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,447 posts
    Seen 2 hours ago
    Registered 12 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 9 months ago
    Registered 12 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

    Still stalking this place, old-school style (UID: 1053)

  • catterz 1 Jun 2007 09:53:59 8,763 posts
    Seen 9 months ago
    Registered 12 years ago
    The values are text so most formulas won't apply. Should've mentioned that before.

    Still stalking this place, old-school style (UID: 1053)

  • Lutz 1 Jun 2007 09:55:35 48,854 posts
    Seen 1 year ago
    Registered 11 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,854 posts
    Seen 1 year ago
    Registered 11 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,447 posts
    Seen 2 hours ago
    Registered 12 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 9 months ago
    Registered 12 years ago
    I just had that idea myself. I guess I'll do that for now then.

    Cheers.

    Still stalking this place, old-school style (UID: 1053)

  • ED209 1 Jun 2007 09:57:50 505 posts
    Seen 8 months ago
    Registered 9 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

    Samantha Janus?

  • Lutz 1 Jun 2007 09:58:29 48,854 posts
    Seen 1 year ago
    Registered 11 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 9 months ago
    Registered 12 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.

    Still stalking this place, old-school style (UID: 1053)

  • catterz 1 Jun 2007 09:58:53 8,763 posts
    Seen 9 months ago
    Registered 12 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 :p

    Still stalking this place, old-school style (UID: 1053)

  • Lutz 1 Jun 2007 10:00:57 48,854 posts
    Seen 1 year ago
    Registered 11 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,487 posts
    Seen 4 years ago
    Registered 9 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,487 posts
    Seen 4 years ago
    Registered 9 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 9 months ago
    Registered 12 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.

    Still stalking this place, old-school style (UID: 1053)

  • reality_cheque 1 Jun 2007 10:35:29 7,487 posts
    Seen 4 years ago
    Registered 9 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 9 months ago
    Registered 12 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.

    Still stalking this place, old-school style (UID: 1053)

  • avtar 1 Jun 2007 11:30:16 94 posts
    Seen 4 years ago
    Registered 9 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,447 posts
    Seen 2 hours ago
    Registered 9 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 4 years ago
    Registered 9 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,447 posts
    Seen 2 hours ago
    Registered 9 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 4 years ago
    Registered 9 years ago
    Stupid question, is it a chart or a table?
  • gang_of_bitches 1 Jun 2007 12:59:40 5,447 posts
    Seen 2 hours ago
    Registered 9 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,487 posts
    Seen 4 years ago
    Registered 9 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,447 posts
    Seen 2 hours ago
    Registered 9 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 5 days ago
    Registered 9 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.
  • Page

    of 10 First / Last

    Previous
Log in or register to reply