Hi all. I cannot figure out how to colour one cell based on the content of another. I have successfully formatted one cell to be red if its empty and orange if it is non empty. However, I don't know how to then change it green if the cell next to it is non-empty. Specifically, column A has an action due date (red if not date entered, orange if a date is entered). Column B is the actual completion date. So if Column B has a date in it, I wish Column A to turn green. Any ideas? Thank you!

alvarezb Select the cells that you want to format conditionally. If more than one, the top left cell should be the active cell in the selection. Note the address of this cell. On the Home tab of the ribbon. select Conditional Formatting > New Rule... Select 'Use a formula to determine which cells to format'. Enter a formula that evaluates to TRUE (or a non-zero number) if the rule should be applied, and to FALSE (or 0) otherwise. In your example, let's say A2 is the active cell in the selection. Use the following formula: =B2<>"" Click Format... Activate the Fill tab. Specify the desired color (green in your example). Click OK, then click OK again.

conditional formatting based on content of another cell

97 Replies

erklizzy
Copper Contributor
Jan 29, 2025
HansVogelaarI'm hoping you're still active! I'm stuck on a similar conditional formatting issue. I have volunteers' info in columns A:D and I want to strikethrough some of them based on the value in column E. Note that in my real spreadsheet, column E is a VLOOKUP formula that is populating either "Yes" or "No" from a table in a separate sheet. I tried this in conditional formatting based on a formula without success.
="$E$2:$E$6=No" applied to =$A$2:$D$6
Here is a onedrive link with simplified data. No vlookup, but it's still not working. Appreciate any help you can give.
2025-01-29 conditional formatting test.xlsx
- HansVogelaar
  MVP
  Jan 29, 2025
  Select A2:D6.
  
  A2 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
  
  =$E2="No"
  
  Click Format...
  Activate the Font tab.
  Select the Strikethrough check box.
  Click OK, then click OK again.
  - erklizzy
    Copper Contributor
    Jan 29, 2025
    You're a hero!
isabellamarie
Copper Contributor
Sep 10, 2024
Hey! You can easily do this with conditional formatting. First, select the cells in Column A that you want to format. Then go to Conditional Formatting and choose "New Rule." Select "Use a formula to determine which cells to format," and in the formula field, enter `=ISNUMBER(B1)` (adjust the row number if needed). This will check if the cell in Column B has a number (like a date) and turn the corresponding cell in Column A green. Just pick your green colour and apply. It’s a simple way to make sure Column A reflects what’s in Column B!
BremnerR
Copper Contributor
Aug 30, 2024
HansVogelaar I was wondering if you could please assist me? I have a register list that I am creating and I would like a range of cells to be populated and highlighted if a certain cell is populated with a particular code. I have tried reading through parts of this thread to see if anything helps but most of it I had already tried to no avail. I think complicating the matter is that all of the columns have a data validation drop down list. For example: If in A1 "FO" is selected from the list, I would like B1:E1 to all be highlighted a particular colour, as well as automatically be populated with "FO" as well, without having to manually do it from their own lists. If possible though, I would still like for the data validation in B1:E1 to then override what is in A1. So if something changes and D1 for example now has to be a different code, I can still manually select it from the drop down list. Thank you.
- HansVogelaar
  MVP
  Aug 30, 2024
  BremnerR
  
  That sounds doable. 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?
  - BremnerR
    Copper Contributor
    Aug 30, 2024
    HansVogelaar I can't find where/how to attach an example? (Can't send through OneDrive.)
BONTKrista
Copper Contributor
Jun 13, 2024
alvarezb

I want to set a condition to a row of cells, with 4 columns. IF one cells in the row of 4 columns has a figure above $0.01 I want the other cells that are NOT above $0.01 to be flagged.

Basically, if one of the cells in the row has a dollar figure above 1-cent and there are others that do not, the ones without should be flagged.
- HansVogelaar
  MVP
  Jun 13, 2024
  BONTKrista
  
  Let's say you want to apply this to D2:G100.
  
  Select this range. D2 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
  
  =AND(D2<=0.01, MAX($D2:$G2)>0.01)
  
  Click Format...
  Activate the Fill tab.
  Select a highlight color.
  Click OK, then click OK again.
Humbledj
Copper Contributor
Apr 10, 2024
Is it possible to duplicate conditional formatting across a spreadsheet? I want to have the rule set up for a specific row to be duplicated for each row below it. For example, If C2="Not Available" then row 2 is formatted in Red font. If C3 is anything else then no formatting for row 3. If C4="Not Available" then row 4 is formatted in red font, etc. How do I copy the formatting rule for each row that is determined by the condition in that respective row's column C?
- HansVogelaar
  MVP
  Apr 10, 2024
  Humbledj
  
  You can use the Format painter, or edit the Applies to range in Conditional Formatting > Manage Rules...
  - Amanda_Connor
    Copper Contributor
    May 16, 2024
    HansVogelaar
    
    I did a conditional formatting formula of =$H13>DATE(2024, 4, 30) for the range A13:AG2603 to highlight all the April expenses with a May invoice date.
    
    It works except it is also highlighting the page header each time it comes up, which is:
    
    ______________Dates_______________
    Invoice Due Discount
    
    The header is in the report 36 times corresponding with the number of pages exported into Excel from another program.
    
    Is there any way to stop this from happening?
laurablow
Copper Contributor
Apr 02, 2024
HansVogelaar Hi,

I am trying to use conditional formatting to change the colour of the text in one cell, based on another cell.

For example, C2 would be either a date (01-Apr-24) or 'N/A'.
Based on the above, I need cells D2:P62 to have a conditional formatting rule that has the text show as red if that cell itself (D2:P62) is populated with a date that's in the past, or show as black if C2 is populated with 'N/A' (this to be the first rule that's checked).

Thanks in advance for your help.
- HansVogelaar
  MVP
  Apr 02, 2024
  laurablow
  
  Select D2:P62.
  
  D2 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
  
  =AND(D2<TODAY(), ISNUMBER($C$2))
  
  Click Format...
  Activate the Font tab.
  Select red as text color.
  Click OK, then click OK again.
  - laurablow
    Copper Contributor
    Apr 02, 2024
    HansVogelaar Thank you for that.
    
    I've realised another requirement for this - where C2 is in the formula, this needs to be from C2:C62.
    
    For example, if C32 reads 31-Mar-2024, I'd like the date in D32 to show as red and so on.
    
    Thanks, once again.
OphirLevy
Copper Contributor
Mar 21, 2024
HansVogelaar I ma trying to select a currency from a drop down list, and based on that, get the values in a table change and the format will be with a $ or a € for example. so based on the selection of currency, wish that the tables will be changing their values, but also the cells will be formatted with the right currency. any suggestions on this?
- HansVogelaar
  MVP
  Mar 21, 2024
  OphirLevy
  
  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? Alternatively, you can attach it to a private message to me. Thanks in advance.
Jrics
Copper Contributor
Mar 19, 2024
I am trying to get a cell to say "NO" when the cell next to it is blank" how do I do this in conditional formatting?
- HansVogelaar
  MVP
  Mar 19, 2024
  Jrics
  
  You don't need conditional formatting for that - you can use a formula.
  
  Let's say you want NO in column B if the corresponding cell in column A is blank, starting in row 2.
  
  In B2:
  
  =IF(A2="", "NO", "")
  
  Fill down.
  - Jrics
    Copper Contributor
    Mar 19, 2024
    Yes I know that but what I failed to mentions the first post was I need the "NO" to be Red and Bold and the "YES" to be Green and Bold.
HansVogelaar
MVP
Apr 13, 2021
alvarezb

Select the cells that you want to format conditionally. If more than one, the top left cell should be the active cell in the selection. Note the address of this cell.

On the Home tab of the ribbon. select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter a formula that evaluates to TRUE (or a non-zero number) if the rule should be applied, and to FALSE (or 0) otherwise.

In your example, let's say A2 is the active cell in the selection.

Use the following formula:

=B2<>""

Click Format...

Activate the Fill tab.

Specify the desired color (green in your example).

Click OK, then click OK again.
- Laurenoflapointe
  Copper Contributor
  Aug 08, 2024
  HansVogelaar
  I am having trouble with the highlight cell rules. I want to create a rule that turns a B cell green if it has the same value as the D cell in it's same row. I need this to apply to both columns though. Everytime I try I can only get the B column to format itself to just one D cell, not the D cell in the same row as a given B cell. In other words it won't let me select a range.
  Can you help?
  - SergeiBaklan
    MVP
    Aug 12, 2024
    Laurenoflapointe
    
    Assuming data is in B2:B7 and D2:D7 apply the rule to combined range as
    
    Formula shall be for the first row in the ranges.
    CF same value in B and D.xlsx10 KB
- AXA2330
  Copper Contributor
  Jul 10, 2024
  HansVogelaar
  How do I format the column next to another if I want it change colour only if the number in the cell to the left is equal to or higher than a 3? I managed to use your previous formula if any number is entered but need one for if a 3 or more is entered and simply can't figure it out!!
  
  Thanks
  - HansVogelaar
    MVP
    Jul 10, 2024
    AXA2330
    
    Let's say you want to conditionally format D2:D100.
    
    Select this range. D2 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
    
    =C2>=3
    
    (C2 is the cell to the left of the active cell)
    
    Click Format...
    Activate the Fill tab.
    Select a fill color.
    Click OK, then click OK again.
- eclecticerkle
  Copper Contributor
  Jun 06, 2024
  HansVogelaar Hello. Is it possible to highlight a range of cells in a single row based on the value of another single cell in that same row, and for this process to be replicated to the rows below? In my case I need the following to apply. If F2>2.5, highlight A2:D2 and if F3>2.5, highlight A3:D3 and so on.
  - HansVogelaar
    MVP
    Jun 06, 2024
    eclecticerkle
    
    Select A2:D100 or however far down you want. The active cell in the selection should be in row 2.
    
    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
    
    =$F2>2.5
    
    Click Format...
    Activate the Fill tab.
    Select a highlight color.
    Click OK, then click OK again.

Forum Discussion

conditional formatting based on content of another cell

97 Replies

Resources