auto cell colour fill

[ashleydhall]

Hi I'm pretty new to excel and havent a clue with vba, I have done a few things with conditional formatting but im now stuck with a auto cell colouring problem.
Basically what i want to do is have the colour of a cell change to red or yellow depending on the value entered compared to the previous cell's value, this is for a weekly sales comparison which is attached {attached copy has cells manually coloured}

e.g.

c11 has the value 5.84 so when i put 3.95 into d11 like i have already done i want the cell to turn red to recognise that its a lower value than the previous cell{weeks} value. if you go to e11 the value is 6.83 highlighted yellow to show an increase.

i have seen conditional formatting to change colour if above or below a certain value but not above or below another cells value.

also this would have to apply to all the cells in i.e. sales row dept named rows i.e. tennis, football etc.

thankyou

ashley

 

[HaHoBe]

Hi, ashley,

have a look at the workbook (using Formula Is instead of Value Is) - there will be no action if the value are equal. I marked the range before applying the Conditional Formatting taking care that D8 is the active cell. Another way would be to use the paintbrush for the copying (click twice for more than one range to fill and click again after having copied all).

Ciao,
Holger

 

[ashleydhall]

thanks exactly what i needed works a treat many thanks again

 

[smiler44]

Ciao,
could you go into a bit more detail as to how you did this please?

Thanks
smiler44

 

[HaHoBe]

Hi, smiler44,

make up the order for the comparisions before starting, mark the area (multiple areas by use of CTRL and mouse or F8 and arrows), the first cell to start and active cell in the ranges was D8. Starting the process with the active cell being the first marked cell makes things easier for me...

Up to Excel2003 menu Format / Conditional Formatting, first option was to compare the cell value of each cell to 0 and leave the color untouched, second was to compare the values of the cell left of the active cell to active cell option active cell is greater and give a color for that (yellow for this case). Condition 3 added is basically the same execpt for comparing smaller and color applied being red.

HTH.

By the way: my first name is Holger.

Ciao,
Holger

 

[smiler44]

Holger,
Very sorry.
I've been playing with the conditional formatting with your instructions, thank you. Neat. I'm a bit unclear on something. When you checked the value of the cell to the left of the active cell did you have to format each cell or could you do a range?
for example to check B1 was greater then then A1 and C1 was greater then B1 and D1 etc was greater then ?1 did you highlight a range and format in one go or did you have to format each cell seperately e.g format if B1 > A1 then colour B1 red then format if C1>B1 format C1 Red then format if D1>C1 format D1 red

smiler44

 

[HaHoBe]

Hi, smiler44,

if you mark a range with the mouse or by using F8 and arrow keys there will always be one active cell which should appear white inside the otherwise shaded area. That´s the basic cell to use and to reference and to put the Conditional Formatting in for, all other cells inside the marked range will follow that. In this example only been relative reference of cells has been used as the comparision covers several columns and rows.

The other way is to copy the Conditional Formatting from one cell by using either Copy/PasteSpecial or the (more handy) paintbrush.

Ciao,
Holger