If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Advanced Conditional Formatting
To those who understand these things better than me...
I have a column of data that contains information about visitors to my website. E.g. Column A 1 | 101 2 | 126 3 | 211 4 | 199 5 | 165 6 | 205 I want to apply some conditional formatting to THAT column, depending on whether the previous number was lower or higher than the number in question. So, If A2A1, then show cell A2 as red (because the number of hits went down for period 2. however, if A2A1, then show cell A2 as green (because the number of hits went up). and if A2 = A1 then show cell A2 as yellow (no change) So, the colours for the cells should be : 1 | 101 not coloured, as is the first set of numbers 2 | 126 green (number went up) 3 | 211 green (number went up) 4 | 199 red (number went down) 5 | 165 red (number went down) 6 | 205 green (number went up) I know that I could simply add an additional column of data, and do A2- A1 to see if the value is negative or not, and then colour the cells according whether a - exists, but i'm trying to produce a spreadsheet with as few columns of data as possible. Any help on this matter would be greatly appreciated. It's really annoying now, because i'm sure that there's a SIMPLE way of doing it, that i've just missed. Thanks in advance, Rachael |
#2
|
|||
|
|||
Advanced Conditional Formatting
Dear Rachael,
While on cell A2, in Format Conditional formatting, select cell value is in the drop down on the left and then select Less than. In the box to the right select cell A1 and remove the $ sign before the row number so that it looks liket $A1. Select the font color desired. Click on Add and add the other 2 conditions as well. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com wrote in message ... To those who understand these things better than me... I have a column of data that contains information about visitors to my website. E.g. Column A 1 | 101 2 | 126 3 | 211 4 | 199 5 | 165 6 | 205 I want to apply some conditional formatting to THAT column, depending on whether the previous number was lower or higher than the number in question. So, If A2A1, then show cell A2 as red (because the number of hits went down for period 2. however, if A2A1, then show cell A2 as green (because the number of hits went up). and if A2 = A1 then show cell A2 as yellow (no change) So, the colours for the cells should be : 1 | 101 not coloured, as is the first set of numbers 2 | 126 green (number went up) 3 | 211 green (number went up) 4 | 199 red (number went down) 5 | 165 red (number went down) 6 | 205 green (number went up) I know that I could simply add an additional column of data, and do A2- A1 to see if the value is negative or not, and then colour the cells according whether a - exists, but i'm trying to produce a spreadsheet with as few columns of data as possible. Any help on this matter would be greatly appreciated. It's really annoying now, because i'm sure that there's a SIMPLE way of doing it, that i've just missed. Thanks in advance, Rachael |
#3
|
|||
|
|||
Advanced Conditional Formatting
Hi,
Select your data excluding A1 then apply 3 conditional formats of Cell Value - Less than A1 Pick Red Cell Value - Greater than A1 Pick Green Cell Value - Equal to A1 Pick yellow Not the references are relative i.e no $ sign Click Ok Mike " wrote: To those who understand these things better than me... I have a column of data that contains information about visitors to my website. E.g. Column A 1 | 101 2 | 126 3 | 211 4 | 199 5 | 165 6 | 205 I want to apply some conditional formatting to THAT column, depending on whether the previous number was lower or higher than the number in question. So, If A2A1, then show cell A2 as red (because the number of hits went down for period 2. however, if A2A1, then show cell A2 as green (because the number of hits went up). and if A2 = A1 then show cell A2 as yellow (no change) So, the colours for the cells should be : 1 | 101 not coloured, as is the first set of numbers 2 | 126 green (number went up) 3 | 211 green (number went up) 4 | 199 red (number went down) 5 | 165 red (number went down) 6 | 205 green (number went up) I know that I could simply add an additional column of data, and do A2- A1 to see if the value is negative or not, and then colour the cells according whether a - exists, but i'm trying to produce a spreadsheet with as few columns of data as possible. Any help on this matter would be greatly appreciated. It's really annoying now, because i'm sure that there's a SIMPLE way of doing it, that i've just missed. Thanks in advance, Rachael |
#4
|
|||
|
|||
Advanced Conditional Formatting
Thanks Ashsih,
With a bit of tweaking, this has done exactly what I wanted... Now for the potential complicated bit... Any way of getting the difference between the cells displayed in a label? Not sure if I need to re-post this now as a new question, but thought i'd ask you first! Thanks again, A v.quick and excellent response! Rachael On 22 Sep, 11:24, "Ashish Mathur" wrote: Dear Rachael, While on cell A2, in Format Conditional formatting, select cell value is in the drop down on the left and then select Less than. *In the box to the right select cell A1 and remove the $ sign before the row number so that it looks liket $A1. *Select the font color desired. *Click on Add and add the other 2 conditions as well. -- Regards, Ashsih Mathur Microsoft Excel MVPwww.ashishmathur.com wrote in message ... To those who understand these things better than me... I have a column of data that contains information about visitors to my website. E.g. * * * *Column A 1 * | * 101 2 * | * 126 3 * | * 211 4 * | * 199 5 * | * 165 6 * | * 205 I want to apply some conditional formatting to THAT column, depending on whether the previous number was lower or higher than the number in question. So, If A2A1, then show cell A2 as red (because the number of hits went down for period 2. however, if A2A1, then show cell A2 as green (because the number of hits went up). and if A2 = A1 then show cell A2 as yellow (no change) So, the colours for the cells should be : 1 * | * 101 * * not coloured, as is the first set of numbers 2 * | * 126 * * green (number went up) 3 * | * 211 * * green (number went up) 4 * | * 199 * * red (number went down) 5 * | * 165 * * red (number went down) 6 * | * 205 * * green (number went up) I know that I could simply add an additional column of data, and do A2- A1 to see if the value is negative or not, and then colour the cells according whether a - exists, but i'm trying to produce a spreadsheet with as few columns of data as possible. Any help on this matter would be greatly appreciated. It's really annoying now, because i'm sure that there's a SIMPLE way of doing it, that i've just missed. Thanks in advance, Rachael- Hide quoted text - - Show quoted text - |
#5
|
|||
|
|||
Advanced Conditional Formatting
Hi,
Thank you for your feedback. If I correctly understand your problem, you would like the difference to be displayed in the cell. If that is indeed the case, then just go to cell A2 and in the formula bar, type =A2-A1 and copy downwards. -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com wrote in message ... Thanks Ashsih, With a bit of tweaking, this has done exactly what I wanted... Now for the potential complicated bit... Any way of getting the difference between the cells displayed in a label? Not sure if I need to re-post this now as a new question, but thought i'd ask you first! Thanks again, A v.quick and excellent response! Rachael On 22 Sep, 11:24, "Ashish Mathur" wrote: Dear Rachael, While on cell A2, in Format Conditional formatting, select cell value is in the drop down on the left and then select Less than. In the box to the right select cell A1 and remove the $ sign before the row number so that it looks liket $A1. Select the font color desired. Click on Add and add the other 2 conditions as well. -- Regards, Ashsih Mathur Microsoft Excel MVPwww.ashishmathur.com wrote in message ... To those who understand these things better than me... I have a column of data that contains information about visitors to my website. E.g. Column A 1 | 101 2 | 126 3 | 211 4 | 199 5 | 165 6 | 205 I want to apply some conditional formatting to THAT column, depending on whether the previous number was lower or higher than the number in question. So, If A2A1, then show cell A2 as red (because the number of hits went down for period 2. however, if A2A1, then show cell A2 as green (because the number of hits went up). and if A2 = A1 then show cell A2 as yellow (no change) So, the colours for the cells should be : 1 | 101 not coloured, as is the first set of numbers 2 | 126 green (number went up) 3 | 211 green (number went up) 4 | 199 red (number went down) 5 | 165 red (number went down) 6 | 205 green (number went up) I know that I could simply add an additional column of data, and do A2- A1 to see if the value is negative or not, and then colour the cells according whether a - exists, but i'm trying to produce a spreadsheet with as few columns of data as possible. Any help on this matter would be greatly appreciated. It's really annoying now, because i'm sure that there's a SIMPLE way of doing it, that i've just missed. Thanks in advance, Rachael- Hide quoted text - - Show quoted text - |
#6
|
|||
|
|||
Advanced Conditional Formatting
Actually, I meant to display the difference in a comment, but I'd
forgotten what the correct term was! So, when I hover over the cell, it will display the difference in the value depending on the previous cell... Thanks again, Rachael On 22 Sep, 12:00, "Ashish Mathur" wrote: Hi, Thank you for your feedback. *If I correctly understand your problem, you would like the difference to be displayed in the cell. *If that is indeed the case, then just go to cell A2 and in the formula bar, type =A2-A1 and copy downwards. -- Regards, Ashsih Mathur Microsoft Excel MVPwww.ashishmathur.com wrote in message ... Thanks Ashsih, With a bit of tweaking, this has done exactly what I wanted... Now for the potential complicated bit... Any way of getting the difference between the cells displayed in a label? Not sure if I need to re-post this now as a new question, but thought i'd ask you first! Thanks again, A v.quick and excellent response! Rachael On 22 Sep, 11:24, "Ashish Mathur" wrote: Dear Rachael, While on cell A2, in Format Conditional formatting, select cell value is in the drop down on the left and then select Less than. *In the box to the right select cell A1 and remove the $ sign before the row number so that it looks liket $A1. *Select the font color desired. *Click on Add and add the other 2 conditions as well. -- Regards, Ashsih Mathur Microsoft Excel MVPwww.ashishmathur.com wrote in message .... To those who understand these things better than me... I have a column of data that contains information about visitors to my website. E.g. * * * *Column A 1 * | * 101 2 * | * 126 3 * | * 211 4 * | * 199 5 * | * 165 6 * | * 205 I want to apply some conditional formatting to THAT column, depending on whether the previous number was lower or higher than the number in question. So, If A2A1, then show cell A2 as red (because the number of hits went down for period 2. however, if A2A1, then show cell A2 as green (because the number of hits went up). and if A2 = A1 then show cell A2 as yellow (no change) So, the colours for the cells should be : 1 * | * 101 * * not coloured, as is the first set of numbers 2 * | * 126 * * green (number went up) 3 * | * 211 * * green (number went up) 4 * | * 199 * * red (number went down) 5 * | * 165 * * red (number went down) 6 * | * 205 * * green (number went up) I know that I could simply add an additional column of data, and do A2- A1 to see if the value is negative or not, and then colour the cells according whether a - exists, but i'm trying to produce a spreadsheet with as few columns of data as possible. Any help on this matter would be greatly appreciated. It's really annoying now, because i'm sure that there's a SIMPLE way of doing it, that i've just missed. Thanks in advance, Rachael- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#7
|
|||
|
|||
Advanced Conditional Formatting
Usually wise to advise the use of =A1
If you just type A1 into the box after selecting equal to, or less than, or whatever, Excel tends to try to be "helpful" and change it to ="A1", thus looking for text string A1, rather than cell reference A1. Typing =A1 into the box should avoid that problem. -- David Biddulph "Mike H" wrote in message ... Hi, Select your data excluding A1 then apply 3 conditional formats of Cell Value - Less than A1 Pick Red Cell Value - Greater than A1 Pick Green Cell Value - Equal to A1 Pick yellow Not the references are relative i.e no $ sign Click Ok Mike " wrote: To those who understand these things better than me... I have a column of data that contains information about visitors to my website. E.g. Column A 1 | 101 2 | 126 3 | 211 4 | 199 5 | 165 6 | 205 I want to apply some conditional formatting to THAT column, depending on whether the previous number was lower or higher than the number in question. So, If A2A1, then show cell A2 as red (because the number of hits went down for period 2. however, if A2A1, then show cell A2 as green (because the number of hits went up). and if A2 = A1 then show cell A2 as yellow (no change) So, the colours for the cells should be : 1 | 101 not coloured, as is the first set of numbers 2 | 126 green (number went up) 3 | 211 green (number went up) 4 | 199 red (number went down) 5 | 165 red (number went down) 6 | 205 green (number went up) I know that I could simply add an additional column of data, and do A2- A1 to see if the value is negative or not, and then colour the cells according whether a - exists, but i'm trying to produce a spreadsheet with as few columns of data as possible. Any help on this matter would be greatly appreciated. It's really annoying now, because i'm sure that there's a SIMPLE way of doing it, that i've just missed. Thanks in advance, Rachael |
#8
|
|||
|
|||
Advanced Conditional Formatting
Hi Rachael
You can only do that with VBA. I think the following code will achieve what you want. I have assumed that data is in column A. Change all references from A to your column letter if different. Sub addComments() Dim lr As Long, i As Long, diff As Long, colour As Long lr = Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False Range("A1:A" & lr).ClearComments For i = 2 To lr diff = Range("A" & i).Value - Range("A" & i - 1).Value If diff 0 Then colour = 45 ' Rose ElseIf diff = 0 Then colour = 43 ' Pale Yellow Else colour = 42 ' Light Green End If Range("A" & i).AddComment Range("A" & i).Comment.Visible = True Range("A" & i).Comment.Text Text:=" " & diff & "" Range("A" & i).Comment.Shape.Select True Selection.ShapeRange.Fill.Visible = msoTrue Selection.ShapeRange.Fill.Solid Selection.ShapeRange.Fill.ForeColor.SchemeColor = colour Selection.ShapeRange.Height = 15.75 Selection.ShapeRange.Width = 28.5 Range("A" & i).Comment.Visible = False Next i Application.ScreenUpdating = True End Sub Copy the Code above Alt+F11 to invoke the VB Editor InsertModule Paste code into white pane that appears Alt+F11 to return to Excel To use Alt+F8 to bring up Macros Highlight the macro name Run -- Regards Roger Govier wrote in message ... Actually, I meant to display the difference in a comment, but I'd forgotten what the correct term was! So, when I hover over the cell, it will display the difference in the value depending on the previous cell... Thanks again, Rachael On 22 Sep, 12:00, "Ashish Mathur" wrote: Hi, Thank you for your feedback. If I correctly understand your problem, you would like the difference to be displayed in the cell. If that is indeed the case, then just go to cell A2 and in the formula bar, type =A2-A1 and copy downwards. -- Regards, Ashsih Mathur Microsoft Excel MVPwww.ashishmathur.com wrote in message ... Thanks Ashsih, With a bit of tweaking, this has done exactly what I wanted... Now for the potential complicated bit... Any way of getting the difference between the cells displayed in a label? Not sure if I need to re-post this now as a new question, but thought i'd ask you first! Thanks again, A v.quick and excellent response! Rachael On 22 Sep, 11:24, "Ashish Mathur" wrote: Dear Rachael, While on cell A2, in Format Conditional formatting, select cell value is in the drop down on the left and then select Less than. In the box to the right select cell A1 and remove the $ sign before the row number so that it looks liket $A1. Select the font color desired. Click on Add and add the other 2 conditions as well. -- Regards, Ashsih Mathur Microsoft Excel MVPwww.ashishmathur.com wrote in message ... To those who understand these things better than me... I have a column of data that contains information about visitors to my website. E.g. Column A 1 | 101 2 | 126 3 | 211 4 | 199 5 | 165 6 | 205 I want to apply some conditional formatting to THAT column, depending on whether the previous number was lower or higher than the number in question. So, If A2A1, then show cell A2 as red (because the number of hits went down for period 2. however, if A2A1, then show cell A2 as green (because the number of hits went up). and if A2 = A1 then show cell A2 as yellow (no change) So, the colours for the cells should be : 1 | 101 not coloured, as is the first set of numbers 2 | 126 green (number went up) 3 | 211 green (number went up) 4 | 199 red (number went down) 5 | 165 red (number went down) 6 | 205 green (number went up) I know that I could simply add an additional column of data, and do A2- A1 to see if the value is negative or not, and then colour the cells according whether a - exists, but i'm trying to produce a spreadsheet with as few columns of data as possible. Any help on this matter would be greatly appreciated. It's really annoying now, because i'm sure that there's a SIMPLE way of doing it, that i've just missed. Thanks in advance, Rachael- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#9
|
|||
|
|||
Advanced Conditional Formatting
I agree, that was an error on my part, thnks
"David Biddulph" wrote: Usually wise to advise the use of =A1 If you just type A1 into the box after selecting equal to, or less than, or whatever, Excel tends to try to be "helpful" and change it to ="A1", thus looking for text string A1, rather than cell reference A1. Typing =A1 into the box should avoid that problem. -- David Biddulph "Mike H" wrote in message ... Hi, Select your data excluding A1 then apply 3 conditional formats of Cell Value - Less than A1 Pick Red Cell Value - Greater than A1 Pick Green Cell Value - Equal to A1 Pick yellow Not the references are relative i.e no $ sign Click Ok Mike " wrote: To those who understand these things better than me... I have a column of data that contains information about visitors to my website. E.g. Column A 1 | 101 2 | 126 3 | 211 4 | 199 5 | 165 6 | 205 I want to apply some conditional formatting to THAT column, depending on whether the previous number was lower or higher than the number in question. So, If A2A1, then show cell A2 as red (because the number of hits went down for period 2. however, if A2A1, then show cell A2 as green (because the number of hits went up). and if A2 = A1 then show cell A2 as yellow (no change) So, the colours for the cells should be : 1 | 101 not coloured, as is the first set of numbers 2 | 126 green (number went up) 3 | 211 green (number went up) 4 | 199 red (number went down) 5 | 165 red (number went down) 6 | 205 green (number went up) I know that I could simply add an additional column of data, and do A2- A1 to see if the value is negative or not, and then colour the cells according whether a - exists, but i'm trying to produce a spreadsheet with as few columns of data as possible. Any help on this matter would be greatly appreciated. It's really annoying now, because i'm sure that there's a SIMPLE way of doing it, that i've just missed. Thanks in advance, Rachael |
Thread Tools | |
Display Modes | |
|
|