Conditional formatting

[Forge101]

I want to be able to change the fill of a cell based on the text placed in another cell.

Example: If I type "BOILER" into a cell in column H (eg H41) then I want the corresponding cell in another column (eg A41) to change colour, but not change the text.

Thank you in advance for any assistance provided.

[#2]

@Forge101 

Here's how you can achieve conditional formatting based on the text in another cell in Excel:

Steps:

1. Select the target cell where you want the color change to occur: In your example, select cell A41 (or the corresponding cell for other rows).

2. Go to the "Conditional Formatting" menu:

   • Click on the "Conditional Formatting" button in the "Home" tab.
   • Alternatively, right-click on the selected cell and choose "Format Cells..." from the context menu. In the "Format Cells" pane, go to the "Fill" tab and click on "Conditional formatting..."

3. Set up the formatting rule:

   • In the "Conditional Formatting Rules Manager" window, choose "New Rule..."
   • Select "Use a formula to determine which cells to format" from the options.

4. Enter the formula:

   • In the "Format values where this formula is true" field, enter the following formula:
   Excel

   =H41="BOILER" // Replace H41 with the cell reference containing the text you want to check

   This formula checks if the value in cell H41 (or the specified cell) is equal to "BOILER" (replace "BOILER" with the desired text). If true, the formatting will be applied.

5. Set the formatting:

   • Click on the "Format..." button to choose the fill color you want to apply when the condition is met.
   • Click "OK" to close the "Format Cells" window.

Explanation:

• The formula =H41="BOILER" compares the value in cell H41 with the text "BOILER". If they match, the formula evaluates to TRUE, and the formatting is applied.
• You can adjust the cell reference in the formula (e.g., =I42) to match the cell containing the text you want to check for in each row.
• You can also modify the formatting options (e.g., font color, border style) as desired.

Additional Notes:

• This approach applies the formatting only to the selected cell. To apply the rule to multiple cells, select the entire range where you want the conditional formatting to be applied before following the steps above.
• You can create multiple conditional formatting rules with different formulas and formatting options to handle various scenarios based on the text in the other cell.

By following these steps, you can effectively change the fill color of a cell based on the text content in another cell, enhancing the visual clarity and organization of your spreadsheet.

[#3]

Hi @smylbugti222gmailcom,

Thank you for your reply. I have managed to get this working, however it's not quite what I am after.

I have a list I am filling in. When I type a word (eg "BOILER" or "NIBE") into a cell on the H column, then I want the same number cell in another column to change colour.

Example 1: I type "BOILER" into H41, then both H41 A41 are highlighted green.
Example 2: I type "NIBE" into H41, then both H41 and A41 are highlighted pink.

I want to apply this rule to the entire column, so going forward, if I type "boiler" into say H234, then both H234 and A234 are highlighted green. If i type "NIBE" then they are highlighted pink, etc.

I thought that the following formula would work but it is not.
=$H:$H "BOILER"

Thank you.

[#4]

@Forge101 

Select column A, then hold down Ctrl while you select column H. H1 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=$H1="BOILER"

The $ before the column letter H is essential.

Click Format...
Activate the Fill tab.
Select green as fill color.
Click OK, then click OK again.

Repeat these steps, but with

=$H1="NIBE"

and pink as color.

Repeat as needed for other values.

[#5]

Hi Hans,

Thank you for your reply. I tried this method but it did not work for me.

Regards.

[#6]

@Forge101 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#7]

Hi @HansVogelaar 

So when I type a word (such as "BOILER") into an "I" cell, I would like that cell, and the corresponding cell in the A column to turn the appropriate colour.

NOTE: I do not want the entire row to turn that colour, just the specified cell(s) in that row.

[#8]

@Forge101 

In your original question, you mentioned column H.

Now it turns out to be column I instead of column H.

So obviously, you should use column I in the conditional formatting formulas:

=$I1="BOILER"

etc.

[#9]

Yes that's right, I did try that.
I understand that people may make that mistake, but I did not 🙂

[#10]

@HansVogelaar 

When you say "Formulas", are you saying that I need to do that for each individual cell in the A column? Because my question is regarding the idea of lots of entries going forward, hence why I want to apply the formula to the entire column with 1 conditional format if possible.

[#11]

Hi @HansVogelaar 

I have figured it out. I do need to apply it to each individual cell, however this is done by applying the formula to a single cell, then dragging it down to the rest. This doesn't do the entire column, but I am able to keep dragging it down as needed.
Thank you for your help, I hope you have a good day! 🙂

[#12]

@Forge101 

If you select columns A and I before creating the rules, as I suggested in my first reply, you don't have to fill or use the format painter. Excel will automatically adjust the rules for all cells in the selection.

[#13]

@HansVogelaar 

Yeah I did try that method to no avail unfortunately.

[#14]

You're on the right track, but the formula needs some adjustments to achieve the desired outcome. Here's how you can modify it:

Revised Formula:

=MATCH($H$2:$H$1000, {"BOILER", "NIBE"}, 0) = ROW()
Explanation:

$H$2:$H$1000: This references the entire range of cells in column H where you want to apply the rule. Adjust the range if your data extends beyond row 1000.
{"BOILER", "NIBE"}: This is an array containing the two text values you want to check for.
MATCH: This function searches for the specified values ("BOILER" or "NIBE") within the provided range and returns the position of the first match. If no match is found, it returns an error (#N/A).
0: This is the match type argument, set to 0 for an exact match.
ROW(): This function returns the current row number.
How it works:

When you enter a text value in a cell within the H2:H1000 range (e.g., "BOILER" in H41), the formula compares it to the values in the array {"BOILER", "NIBE"}.
If there's a match (e.g., "BOILER" matches the first element), the MATCH function returns 1.
The ROW() function also returns the current row number (e.g., 41).
Since both values are equal (1 = 41), the condition is true, and the formatting is applied to both cells in that row (H41 and A41).
If the entered text doesn't match any value in the array (e.g., "NIBE" doesn't match), the MATCH function returns an error (#N/A), and the condition becomes false. Consequently, no formatting is applied.
Applying the Rule:

Select the entire column A where you want the color change to occur.
Follow the steps outlined in the previous response to access Conditional Formatting.
Choose Use a formula to determine which cells to format.
Paste the revised formula in the formula box.
Set the desired formatting for cells that meet the condition (e.g., green fill for "BOILER", pink fill for "NIBE").
Click OK to apply the rule.
Now, whenever you type "BOILER" or "NIBE" in any cell within the H2:H1000 range, the corresponding cells in both columns H and A will change color based on the matching text.

Additional Notes:

You can expand the array in the formula to include more text values for additional color formatting rules.
Adjust the formatting options within the conditional formatting settings to define different colors for each matching text.
This approach ensures that the conditional formatting applies dynamically throughout the entire column based on the text entered in the corresponding cells of column H.