Forum Discussion
Solved
conditional formatting based on content of another cell
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!
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.
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.
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.You're a hero!
- 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!
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.
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?
HansVogelaar I can't find where/how to attach an example? (Can't send through OneDrive.)
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.
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.
- 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?
You can use the Format painter, or edit the Applies to range in Conditional Formatting > Manage Rules...
- 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?
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.
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.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.
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?
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.
- 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?
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.
- 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.
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.
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?
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.
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
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.
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.
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.
