XL n CAD

Excel isn’t just a tool – it's a way of Life!

Highlight a Cell or Cells according to the Entry made in a particular Cell

In this article, I will explain how to apply Conditional Formatting to a cell or cells, in such a way that the background color and font style of that cell or cells change according to entry made in a particular cell.

Suppose you want to highlight the cell C5 in Green color when you enter the text ‘Google’ in it,

Select the cell C5 > Go to Conditional Formatting in the Home tab > New Rule

The dialog box called New Formatting Rule is activated.

Select the option, Use a formula to determine which cells to format > Type in the following formula into the input box under the label ‘Format the values where this formula is true‘,

=$C$5="Google"

Now, define the formatting to be applied to the cell, whenever the above formula is evaluated as TRUE. In this case, we need the cell to be highlighted in Green color whenever the formula is evaluated as TRUE.

Click on Format > Go to the Fill tab in the Format Cells dialog > Select Green color > Click OK

In the New Formatting Rule dialog, we will have the preview of formatting to be applied.

Click OK. Now when you type in the text Google into the cell C5, the cell will be highlighted in Green color.

Highlight a range of cells

To highlight a range of cells according to the entry made in a cell, select the cells and repeat the above procedure.

Suppose if you want to highlight the cells from A9:E9 in Yellow color and apply Bold Italic font style, when the text ‘Amazon’ is entered into the cell A1,

Select the cells from A9:E9 > Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format > Type in the following formula

=$A$1="Amazon"

Click on Format > Go to the Fill tab in the Format Cells dialog > Select Yellow color > Go to Font Tab > Select Bold Italic font style > Click OK

In the New Formatting Rule dialog, we will have the preview of formatting to be applied.

Click OK to apply the rule.

Now, when you enter the text Amazon into the cell A1, the cells from A9:E9 will be highlighted in yellow color and Bold Italic font style will be applied to the text in those cells.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.