small doubt in conditional formatting

[san_jayd]

i have prepared a sheet. where the values in the sheet are copied using marco.

i need to apply conditional formatting to the sheet.

example

x | Y

2 | 5

3 | 2

here the X values are the reference values. if the y is greater than x the cell should be in green and red colour for the other way.

i tried for a single cell i able to get the conditional formatting when i copy the condition to other cell. the reference is take from the X first value (2 in this above example). so for the send row still i get green colour instead of red.

 

[DaveTurton]

Why don't you apply the colour when running the macro


i.e.

[pre]

If Activecell.Offset(0,1).Value > Activecell.Value Then
Activecell.Interior.ColorIndex = 4 'colour Green
Else
Activecell.Interior.ColorIndex = 3 'colour Red

[/pre]

The below link has the colour index values:


http://dmcritchie.mvps.org/excel/colors.htm

 

[DaveTurton]

If you don't want to do it as part of your Macro. When you create a conditional format, remove the absolute reference ($) before copying the format down

 

[san_jayd]

i don't want in the macro . as i need to change the reference values ie. X column frequently. as using macro here my row and column header are in numbers only.

the cell numbers are like RC[13]. is there any way to do it.

 

[DaveTurton]

Can you post an example workbook?


Please refer to http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[NARAYANK991]

Hi ,


The numbers are shown in R1C1 reference style ; if you go into Excel Options , Formulas , and uncheck the R1C1 Reference Style checkbox , you will see the formulae in what is called A1 reference style.


In the R1C1 reference style , the brackets [] indicate a relative reference ; RC13 refers to the same row , and 13 columns to the right ; depending on where this formula is entered , the actual formula can be anything. If we assume that the formula =RC[13] is entered in cell A57 , the actual formula in A1 reference style will be =N57.


Narayan

 

[nazmul_muneer]

Dear San_jayd,

Think your data is in Col-A and Col-B

Go to B2

Home>Conditional Formatting>New Rule

>Select-Use a formula to determine which cells to format

>Type =B2>A2 in - Formula values where this formula is true

>Click on format button

>Fill

>Select green color and click OK twice

Now copy format and paste down


Done!


nazmul_muneer

 

[san_jayd]

i have put the demo file in the location

https://dl.dropboxusercontent.com/u/83159368/demo.xlsx

here i want the conditional formatting in column name trial1 and trial2

i was able to put in a single cell when tried to copy to other cells i am not able to do it.

 

[SirJB7]

Hi, san_jayd!


Your CF conditions where almost right, adjust them to this:

=C2>=$B2 for green

=C2<$B2 for red


Set range to apply as $C$2:$D$7.


Regards!