Conditional Formatting - Based on other cell Value/format?

Thanks:  0
Likes:  0

# Thread: Conditional Formatting - Based on other cell Value/format?

1. ## Conditional Formatting - Based on other cell Value/format?

Afternoon,

Second question of the day regarding conditional formating...

Is it possible to alter the format of a range of cells (using conditional formatting) based on a value/format in another cell?

I have two columns (A/B) which turn dark red if column B>A.

I would like conditional formatting to turn column C to AO the same color if B>A.

Unfortunately, I can only seem to get the format to work based on the value of the specific cell in column C to AO.

Thanks!

2. ## Re: Conditional Formatting - Based on other cell Value/forma

Couldn't you use the same conditional format you used for A/B?

3. ## Re: Conditional Formatting - Based on other cell Value/forma

In C1, make the conditional format

Formula Is: =\$B1>\$A1

and copy it across to AO and down however far you need to go.

4. ## Re: Conditional Formatting - Based on other cell Value/forma

Thanks, for some reason I wasn't getting the "Formula Is" bit.

Have a wrench to throw into this if you guys wouldn't mind giving your input...

First, is I have four conditions which I would like to evaluate to 3 colors.
B>A, Green
B=A, Red
B= "End" or "Home", Gray

I'm guessing I can figure out how to take care of the Grey with an OR statement.

Problem I'm seeing (before even dealing with the "OR" bit of it) is that it won't evaluate to Gray because its interpreting "END" and "Home" numerically and saying oh, its >A, make it Red!

5. ## Re: Conditional Formatting - Based on other cell Value/forma

When conditional formats get complicated, I usually switch to VBA. It would be pretty easy to write a macro for this... or do you need a formula solution?

6. Yep, was able to work in an OR statement for one condition, but the other two conditions are still overwriting that condition by mistakenly evaluating "END" and "HOME" numerically...

7. Not familar with VBA at all, was hoping for a quick fix with conditional formatting.

Only issue i'm having now is that the conditional format is looking at the two text values as numbers...

8. This appears to be working for me.

B>A:
=AND(ISTEXT(B1)=FALSE,B1>A1)

B=A:
=AND(ISTEXT(B1)=FALSE,B1=A1)

End/Home:
=AND(ISTEXT(B1)=TRUE,OR(B1="End",B1="Home"))

However, even if both cells are blank, it will result in the A=B condition.

9. ## Re: Conditional Formatting - Based on other cell Value/forma

I think this will work. Here is your B1 conditional format screen (They MUST be in this order)

Condition 1:
Cell Value Is: equal to: ="Home" --> format grey
Condition 2:
Cell Value Is: equal to: ="End" --> format grey
Condition 3:
Cell Value is: greater than: =\$A1 --> format red

Copy down

Conditional format for C1 is:

Condition 1:
Formula Is: =\$B1="Home" -->format grey
Condition 2:
Formula Is: =\$B1="End" --> format grey
Condition 3:
Formula Is: =\$B1>\$A1 --> format red

Copy down and over to AO

Let me know...

10. ## Re: Conditional Formatting - Based on other cell Value/forma

Originally Posted by yee388
Condition 1:
Formula Is: =\$B1="Home" -->format grey
Condition 2:
Formula Is: =\$B1="End" --> format grey
Condition 3:
Formula Is: =\$B1>\$A1 --> format red

Copy down and over to AO

Let me know...
Only problem is you left out the A=B condition

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•