Conditional Formatting

[Keerthivasan]

Hi,

I am looking for color change in a particular cell when the value is changed. Below is an example

If the value in cell E1 is less than 10, it should be red
If the value in cell E2 is greater than 10, should be green
If the cell is blank, should be black

The challenge am updating the value in cell E1 and the conditional formatting is also in the same cell

Please help

Keerthi

 

[Michael M]

Hi and welcome to the Board
So, how does the value in E2 change ??

 

[Matt Rogers]

Hi.

Sorry, i haven't understood exactly.
Maybe you mean this?

Excel Workbook
	E
1	10
2	11
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E1	1. / Condition Blanks	Abc
E1	2. / Formula is =(E2>10)	Abc
E1	3. / Cell Value less than =10	Abc

 

[Keerthivasan]

I apologize for my delayed response

May be I'll put my question again with examples. This is basically to easily identify how many have passed/failed in the test. The pass mark is 10

If the value in cell E:E is between 0 and 9, it should be red
If the value in cell E:E is greater than or equal to 10, should be green
If the cell E:E is blank, should be black

COLUMN D COLUMN E
<TABLE style="WIDTH: 110pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=147 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 height=18 width=71>NAME</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 57pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl30 width=76>TEST</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 height=17>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl28 align=right x:num>10</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 align=right x:num>7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>V</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ccffcc; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl23 align=right x:num>11</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: red; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl24 align=right x:num>5</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=18>D</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: black; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl26> </TD></TR></TBODY></TABLE>


Please help

Keerthi <!-- / message -->

 

[Michael M]

ADVERTISEMENT

Matt has pretty well covered it !
but I'd use in the Conditional formatting

Format 1. Cell Value = ""
Format 2. Cell Value >= 10
Format 3. Cell Value < 10

 

[Keerthivasan]

Thanks... My only challenge is with blank cells.

Condition blank is not working if I give the value "" in conditional formatting. Any alternates pls?

K

 

[Michael M]

ADVERTISEMENT

For blanks try

formula = ISBLANK(E1)

 

[Keerthivasan]

Even if I enter formula ISBLANK(E1), it is not showing black but red

 

[Rraju]

Hi Keerthi

I havetried this,is working:

1) Cell Value > 10 -> Set Green Color
2) Cell contains 'Blank' -> Set Black Color
3) Cell contains 'Non Blanks' -> Set Red Color

 

[Keerthivasan]

Somehow the blank condition is still not working as expected. Am not sure if am missing something. Is it because of MS-2003? Can someone please attach the file with formulas. Think it will be helpful