SOLVED

# Conditional Formating Formula

Copper Contributor

# Conditional Formating Formula

I have two columns with numbers.

If the number in B column is higher than the number in A column highlight the cell green

If the number in B column is lower than the number in A column highlight the cell red

If the number in B column is within 10% of the number in A column highlight the cell yellow

What formula would you use?

Thank you!

8 Replies
best response confirmed by daniel315 (Copper Contributor)
Solution

# Re: Conditional Formating Formula

you would need 3 rules for conditional formatting:

Yellow: =AND(B1>=A1*0,9;B1<=A1*1,1)

Red:  =B1<A1

Green:  =B1>A1

Depending on your regional settings it might be that you need to replace the first formula like this:

=AND(B1>=A1*0.9,B1<=A1*1.1)

# Re: Conditional Formating Formula

Just about point 3, you mean if B bigger or small than A within %10.

You need to do three rules in the following order, because there will overlap within the %10

=AND((B1>=A1*0.9),(B1<=A1*1.1))        (yellow)

=B1>A1    (green)

=B1<A1    (red)

# Re: Conditional Formating Formula

Thank you so much, DTE!

# Re: Conditional Formating Formula

Awesome! Thank you so much, Jihad!

# Re: Conditional Formating Formula

@Martin_Weiss  I could use your expertise with an Excel conditional formatting issue. I want to highlight a row in blue if the value in cell L867 remains "Not contacted yet" for 40 minutes. The L column has a dropdown list, and I'm a bit stuck on the formula. Your assistance would be greatly appreciated.

Thanks a bunch!

# Re: Conditional Formating Formula

it's not completely clear to me, what the exact rules should be for your formatting. When you say: "not yet contacted" for 40 minutes:

40 minutes related to what exactly? To a certain record in the list? Which one?

I also propose to put your question in a complete new thread, so it gets more attention by other users.

Cheers,

Martin

# Re: Conditional Formating Formula

Hi @Martin_Weiss,

When I need to reach out to a candidate, I have 40 minutes to do so. Once I make contact, I update their status from 'Not Contacted Yet' to another one. Now, what I'm looking for is a rule that says if the status doesn't change from 'Not Contacted Yet' within 40 minutes, the entire row should turn blue automatically.

# Re: Conditional Formating Formula

that's a bit tricky because you need to calculate the time difference between current date/time and the date/time of the call.

To do this, you need to combine the date and the time of the call (add them up). Then you can calculate the difference to the current time by using the NOW() function.

The formula in the formatting rule would be:

=AND((NOW()-(\$A3+\$B3))*24*60>40;\$C3="Not contacted yet")

In column E you see the time difference in minutes. This column is not important for the solution, it just illustrates how the formula works.

Hope this helps.

Cheers,
Martin

1 best response

Accepted Solutions
best response confirmed by daniel315 (Copper Contributor)
Solution

# Re: Conditional Formating Formula

you would need 3 rules for conditional formatting:

Yellow: =AND(B1>=A1*0,9;B1<=A1*1,1)

Red:  =B1<A1

Green:  =B1>A1

Depending on your regional settings it might be that you need to replace the first formula like this:

=AND(B1>=A1*0.9,B1<=A1*1.1)