# Conditional Formatting Gone Wild

#### JAYLEECAKE

##### New Member
I am losing my mind over this. I am very well acquainted with Excel; however, this super basic formula is killing me. Have I completely lost it?!

We get a task on certain dates (DATE) and depending on the task, it may either have a 75-day suspense or 180-day suspense (STANDARD).

To find out the number of days the tasker has been active, I calculated the DAYS column with [=DAYS(TODAY(),\$A2)]. Works great.

To find out how overdue something is, I calculated the "percent" (though not *100) with [=\$C2/\$B2]. Works great.

PROBLEM! When I use conditional formatting to highlight in red the cells in PERCENT that are greater than 1 [=\$D2>1], it goes wild and highlights Row 2 as well even though \$D\$2=2.43.

(I will also be adding that if it's greater than or equal to .73 but less than 1 it should be YELLOW. Otherwise, GREEN.)

I have tried this on multiple computers, on brand new spreadsheets, etc. I have tried enclosed IF functions in a separate column to give a RED, GREEN, YELLOW answer, then base the conditional formatting off of that. IT STILL HIGHLIGHTS INCORRECT ROWS! I have no idea what is going on. I feel like I am losing my mind over here...

PLEASE HELP!

Formats: DATE=Date, STANDARD, DAYS, PERCENT all = Number

#### Attachments

• ExcelIssue.jpg
147.2 KB · Views: 16

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### jtakw

##### Well-known Member
Hi,

The number 1 (one) in percentage terms is 100%, so you want the percentage equivalent of the number 1, you'll need to use 0.01
.73 is actually 73%, etc.

#### JAYLEECAKE

##### New Member
Hey there, I didn't actually turn them into percentages. They are still just the decimal (that's why I said I didn't multiply by 100). If you look at the attached screenshot, you'll see what I mean.

Thanks! - J

#### DRSteele

##### Well-known Member
You need to change D2 to D1 in the CondFrmt formula because you've selected the entire C column.

#### JAYLEECAKE

##### New Member

ADVERTISEMENT

OH. MY. Wow. Thank you! If I turn it into a table (with headers), would I still need to use D1 instead of D2?

#### DRSteele

##### Well-known Member
Just turn into a Table, select the Days column and create the formula with =D2>1. When you add or remove Table Rows, the CondFrmt rule will adjust.

#### JAYLEECAKE

##### New Member

ADVERTISEMENT

You rock! Thank you!

#### DRSteele

##### Well-known Member
I blush. You're welcome,

By the way, this video from ExcelIsFun on the youtube really lays it all out.

#### jtakw

##### Well-known Member
Hey there, I didn't actually turn them into percentages. They are still just the decimal (that's why I said I didn't multiply by 100). If you look at the attached screenshot, you'll see what I mean.

Thanks! - J

Oops, missed that part, glad you got sorted out now.

Replies
22
Views
550
Replies
2
Views
65
Replies
1
Views
145
Replies
3
Views
113
Replies
7
Views
329

Threads
1,127,504
Messages
5,625,186
Members
416,077
Latest member
SJSB

### 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.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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