# Conditional Formatting

#### Keerthivasan

##### New Member
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

Keerthi

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### Michael M

##### Well-known Member
Hi and welcome to the Board
So, how does the value in E2 change ??

#### Matt Rogers

##### Well-known Member
Hi.

Sorry, i haven't understood exactly.
Maybe you mean this?
Excel Workbook
E
110
211
Sheet
Cells with Conditional Formatting
CellConditionCell FormatStop If True
E11. / Condition BlanksAbc
E12. / Formula is =(E2>10)Abc
E13. / Cell Value less than =10Abc

#### Keerthivasan

##### New Member
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>

Keerthi <!-- / message -->

#### Michael M

##### Well-known Member
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

##### New Member
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

##### Well-known Member
For blanks try
Code:
``formula = ISBLANK(E1)``

#### Keerthivasan

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

#### Rraju

##### New Member
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

##### New Member
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

Replies
1
Views
298
Replies
3
Views
161
Replies
8
Views
277
Replies
16
Views
389
Replies
14
Views
162

1,195,924
Messages
6,012,336
Members
441,691
Latest member
starlightmuse

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back