Conditional Format Based on Another Cell's Value

eforti

Board Regular
Joined
Aug 15, 2005
Messages
222
Hello All,

I am trying to find certain text within a given cell and have it affect the conditional format of another cell.

In column A I have a model number, say "**ABC123", (note that it has two asteriks in front of it), and in other columns I have miscellaneous cell values that I'd like to apply the conditional formatting to. I'd like to make it so that if the cell in the corresponding row, and in column A has "**" in the text, the cell format changes.

A__________________B
**ABC123________Building
ABC123__________Building

In this example B1 would be bolded conditionally because cell A1 contains two asteriks.

Does anyone have any ideas that may help?

Thanks in advance

P.s. I have tried the following:

=ISNUMBER(FIND, =ISNUMBER(SEARCH, =ISNUMBER(MATCH,
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
You can try this formula in the FormulaIs option:

=IF(ISNUMBER(SEARCH("~**",A1)),TRUE,FALSE)

and set the formatting to bold.
 
Upvote 0
Thanks for the reply Seti. This still does not solve my problem unfortunately. Nothing in the cell changes.
 
Upvote 0
Thanks for the reply Seti. This still does not solve my problem unfortunately. Nothing in the cell changes.

A shortened version of Seti's

=ISNUMBER(SEARCH("~**",A1))

Is there another condition besides the two *?
 
Upvote 0
I do have one other conditional format applied to the cells but that condition changes the background color, not the font. The only things that I could think of that are out of the ordinary is that I have some columns hidden, I have one conditional format alreay in place....and that's it. There is nothing else out of the ordinary with this sheet.
 
Upvote 0
I do have one other conditional format applied to the cells but that condition changes the background color, not the font. The only things that I could think of that are out of the ordinary is that I have some columns hidden, I have one conditional format alreay in place....and that's it. There is nothing else out of the ordinary with this sheet.

Can you post the other CF?
 
Upvote 0
Absolutely...

If the cell value is greater than 0 the background should be white. This condition is applied to H5:R26, which is the same range I am trying to apply this new CF to.
 
Upvote 0
Absolutely...

If the cell value is greater than 0 the background should be white. This condition is applied to H5:R26, which is the same range I am trying to apply this new CF to.

Try,

=N(A1)
 
Upvote 0
Conditional formats occur in order so once you have a true condition and apply one, none of the others will be applied. So for example, if you have set the background to white, no other conditions are checked. Even if the ** appears, CF won't test for it.

Brian, thanks for the cleaner code.
 
Upvote 0
Well that certainly explains why it isn't working in the grand scheme of things. Thanks for the clarification. I decided to delete the first condition to see what I could work out and I still can't get this condition to work.

Conditional Format for Cell B1 is =ISNUMBER(SEARCH("~**",A1))

A1 = **ABC123
B1 = Building

The conditional format is applied to Cell B1, and is not changing anything in it. Am I setting this up incorrectly?
 
Upvote 0

Forum statistics

Threads
1,223,165
Messages
6,170,465
Members
452,329
Latest member
Irefsports

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