# 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...

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

#### Attachments

• ExcelIssue.jpg
147.2 KB · Views: 24

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

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

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

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.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,012
Messages
5,834,919
Members
430,326
Latest member
tomwax46

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

### Which adblocker are you using?

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