Conditional Formatting

technofrank

New Member
Joined
Jun 8, 2015
Messages
4
Hi All,

Please help me about conditional formatting, suppose I will type an exact word or value "TEST" in cell B2. Is there a way after typing the word "TEST" in cell B2 the cells from A2 to F2 will change to my desired fill color?

I know there's a way out here please help.

I am using MS OFFICE 2007

Thank you in advance.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Sixthsense

Active Member
Joined
Nov 19, 2012
Messages
385
In the Color box, select Red. In the Font Style box, select Bold.Click OK until the dialog boxes are closed.


  1. Select cells A2:F2.

  2. Then, click Home > Conditional Formatting > New Rule.

  3. In the New Formatting Rule dialog box, click Use a formula to determine which cells to format.

  4. Under Format values where this formula is true, type the formula: =COUNTIF($A$2:$F$2,"Test")


Apply your desired formatting :)

 

technofrank

New Member
Joined
Jun 8, 2015
Messages
4
Sorry I have another question, what if in any cell under column B if i put the word test their corresponding rows will be automatically change to my desired fill color.

for example if I type test in B3 the cell A3 to F3 will change color to blue;
if I type test in B6 the cell A6 to F6 will change color to blue;
if I type test in B10 the cell A10 to F10 will change color to blue;

Thank you in advance
 

Sixthsense

Active Member
Joined
Nov 19, 2012
Messages
385

ADVERTISEMENT

Follow the same steps mentioned in Post #3 but select the range and proceed.

change the Post #2 formula with the below one.

=COUNTIF($A2:$F2,"Test")
 

technofrank

New Member
Joined
Jun 8, 2015
Messages
4
It's working but there's a flaw, for example if i type test in any cell inside the range that i selected the conditional formatting will still work. Is there any way that only cells in column B will accept the conditional formatting, because I tried to type test in B2 it works and I tried to type test in C2 it will still work.

Thank you in advance
 

Sixthsense

Active Member
Joined
Nov 19, 2012
Messages
385
I regret for the inconvenience.

Replace the provided formula with the below one

=$B2="Test"
 

Watch MrExcel Video

Forum statistics

Threads
1,127,205
Messages
5,623,363
Members
415,969
Latest member
Rey99

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
Top