Conditional Formatting - Based on other cell Value/format?

Cerestes

Board Regular
Joined
Jan 31, 2004
Messages
185
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!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Re: Conditional Formatting - Based on other cell Value/forma

Couldn't you use the same conditional format you used for A/B?
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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...
 
Upvote 0
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...
 
Upvote 0
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. :confused:
 
Upvote 0
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...
 
Upvote 0
Re: Conditional Formatting - Based on other cell Value/forma

yee388 said:
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 ;)
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

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?

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
Back
Top