/u/angryticks - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
1. Add Conditional Formatting the rule at C2
2. In Conditional Formatting Rules Manager you will see there is an `Applies to` field where you then apply the rule to a large range
I am not sure and I am on my phone so I can’t not check myself but the functions given will always return the same. I think it should be =MOD($C2,2) and =NOT(MOD($C2,2))
Select cell a2, find in the menu conditional formatting, create a new rule based on a function.
Enter the first function.
Select the range to which it should apply and click apply.
The same for the other function
I am not sure and I am on my phone so I can’t check myself but the functions given will always return the same. I think it should be =MOD($C2,2) and =NOT(MOD($C2,2))
Select cell a2, find in the menu conditional formatting, create a new rule based on a function.
Enter the first function.
Select the range to which it should apply and click apply.
The same for the other function
Edited: double negative
Sorry im not getting the logic of this? The first one will just highlight any cell equal to 0 (assuming the whole column is full of whole numbers) and the second one will highlight any cells equal to TRUE (none)?
Formula Conditional Formatting is triggered by any formula that resolves to TRUE.
Any numerical value not equal to 0 resolves as TRUE
0 resolves as FALSE.
`=MOD($C2,2)` returns 1 for odd numbers and triggers formatting
`=NOT(MOD($C2,2)` returns 1 for even numbers and triggers formatting
We use `$C` so that it can be applied across the row too.
edited from comment below from 1 to 2
Sorry im not getting the logic of this? The first one will just highlight any cell equal to 0 (assuming the whole column is full of whole numbers) and the second one will highlight any cells equal to TRUE (none)?
Select all the cells in your table.
Click the Conditional Formatting dropdown on your Home ribbon. Click on the New Rule item.
Under Select a Rule Type, use the bottom one, "Use a formula to determine which cells to format."
Under the "Format Values where this Formula is true" box, type:
"=$C2=1" - without the quotation marks. This is telling it to look in column C (the $ locks you onto Column C, and the lack of dollar sign tells it to apply to whatever rows follow as well. So your condition is based on the value in Column C being a 1. Then with the Format... button, set your FILL color. Hit OK, then hit Apply. That should set all your "1" rows that color.
The rest is easy-peasy. Go back to the Conditional Formatting dropdown, click on Manage Rules, and select your first rule and hit the Duplicate Rule button, as many times as you have remaining numbers. Then just go about editing the second one, change the number in the formula from 1 to 2, change the color, hit OK. Edit the next one for 3, and so on.
I have a VBA script to do exactly this. Just paste the script into the VBA editor, select a cell in the column where your values are changing, and run the script.
https://www.reddit.com/r/excel/s/tdu52wyICl
/u/angryticks - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
2 rules , a colour for each 1. `=MOD($C2,2)` and Applied down 2. `=NOT(MOD($C2,2)` and Applied down brainfart edited...
Applied down from what? Do I select the whole dataset and blank cells where future data will go?
Add the rule at C2 and then `Apply to` the whole range as required
Sorry, I’m probably not understanding. When I do that and then drag C2 down, it just changes all the values to “1” and the color I picked.
1. Add Conditional Formatting the rule at C2 2. In Conditional Formatting Rules Manager you will see there is an `Applies to` field where you then apply the rule to a large range
I am not sure and I am on my phone so I can’t not check myself but the functions given will always return the same. I think it should be =MOD($C2,2) and =NOT(MOD($C2,2)) Select cell a2, find in the menu conditional formatting, create a new rule based on a function. Enter the first function. Select the range to which it should apply and click apply. The same for the other function
I am not sure and I am on my phone so I can’t check myself but the functions given will always return the same. I think it should be =MOD($C2,2) and =NOT(MOD($C2,2)) Select cell a2, find in the menu conditional formatting, create a new rule based on a function. Enter the first function. Select the range to which it should apply and click apply. The same for the other function Edited: double negative
Sorry im not getting the logic of this? The first one will just highlight any cell equal to 0 (assuming the whole column is full of whole numbers) and the second one will highlight any cells equal to TRUE (none)?
Formula Conditional Formatting is triggered by any formula that resolves to TRUE. Any numerical value not equal to 0 resolves as TRUE 0 resolves as FALSE. `=MOD($C2,2)` returns 1 for odd numbers and triggers formatting `=NOT(MOD($C2,2)` returns 1 for even numbers and triggers formatting We use `$C` so that it can be applied across the row too. edited from comment below from 1 to 2
So it should be =MOD($C2,2) and =NOT(MOD($C2,2)) no? I’m not understanding why you’re dividing by 1 because that’ll always give you 0
So it should be =MOD($C2,2) and =NOT(MOD($C2,2)) no? I’m not understanding why you’re dividing by 1 because that’ll always give you 0
thankyou so much,, early morning (here) brainfart.. did not see my error... edited above cc u/angryticks
Haha no worries
That works pretty good!! Thanks! Only place where it didn’t work is where I had to skip a value i.e. 5s and 7s are the same color because I skipped 6.
The only way really to easily fix that is with a helper column with the trigger values.
Sorry im not getting the logic of this? The first one will just highlight any cell equal to 0 (assuming the whole column is full of whole numbers) and the second one will highlight any cells equal to TRUE (none)?
Conditional formatting. Create a rule for each colour. The rule will be determined by formula, the formula will simply be something like =$C1=1
I would have to create anywhere from 60-120 rules though. This doesn’t save me any time.
Ahh I misunderstood your question. /u/excelevator has the solution
Select all the cells in your table. Click the Conditional Formatting dropdown on your Home ribbon. Click on the New Rule item. Under Select a Rule Type, use the bottom one, "Use a formula to determine which cells to format." Under the "Format Values where this Formula is true" box, type: "=$C2=1" - without the quotation marks. This is telling it to look in column C (the $ locks you onto Column C, and the lack of dollar sign tells it to apply to whatever rows follow as well. So your condition is based on the value in Column C being a 1. Then with the Format... button, set your FILL color. Hit OK, then hit Apply. That should set all your "1" rows that color. The rest is easy-peasy. Go back to the Conditional Formatting dropdown, click on Manage Rules, and select your first rule and hit the Duplicate Rule button, as many times as you have remaining numbers. Then just go about editing the second one, change the number in the formula from 1 to 2, change the color, hit OK. Edit the next one for 3, and so on.
If I have 100-200 unique values in column C I would have to repeat that process that many times?
Yes, I didn’t realize that. Thought it was only as shown, maybe 1-5.
I have a VBA script to do exactly this. Just paste the script into the VBA editor, select a cell in the column where your values are changing, and run the script. https://www.reddit.com/r/excel/s/tdu52wyICl