Using Conditional Formatting for every third column

[JoshTheTofu]

Hi guys

I was wondering whether there was an easier way to do this?

I am trying to use conditional formatting to change the colour of the text for the second and third columns based on whether the number in the third column is positive.

I have been making rules for each column individually but now I want the sheet to get a lot bigger, this will take me until next lockdown to finish this.

Is there anything I can put into the formula box to make this happen?

Kind regards

Josh

 

[Joe4]

I am pretty sure there is something we can do, using the MOD function (which is the remainder after dividing a number by another) and the COLUMN() function (which will return the column number that the formula is placed in).

For example, enter this in cell A1 and copy across a bunch of columns, and see the pattern that it produces:

=MOD(COLUMN(),3)

See how the pattern repeats every three columns?

So you can use this logic in your Conditional Formatting to apply the same rule to th whole range at once, and identify every third column.

If you need help setting up the rule, please provide us with a simple example. Be sure to indicate exactly which column each piece is in so we can make sure that the formula is set up correctly.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[JoshTheTofu]

Hi Joe

Thank you for the help.

I have used the above code to make all the second and third columns change colour but would like to get it so that they only change colour if the number in the third column is positive.

Below is a table example of what I was looking for:

Total Expected	Total Actual	Difference	Total Expected	Total Actual	Difference	Total Expected	Total Actual	Difference
11	7	-4	8	5	-3	8	5	-3
6	7	+1	6	6	0	6	7	+1
45	39	-6	64	62	-2	64	63	-1
21	18	-3	40	36	-4	40	37	-3
10	6	-4	24	31	+7	24	23	-1
8	6	-2	6	5	-1	6	5	-1
6	7	+1	6	5	-1	6	5	-1

As you can see, every positive number in the third column has turned that and the second column red, that is what I would like to achieve.

I tried

=MOD(COLUMN()+1,3)>0

But that didn't seem to work, I am really stumped.

 

[Joe4]

OK, assuming that your data example, is in the range A1:I8, I would do this with two Conditional Formatting formulas:

First CF Rule
1. Select the range B2:I8
2. Go to Conditional Formatting
3. Click New Rule
4. Select "User a formula to determine which cells to format"
5. Enter the following formula:

=AND(MOD(COLUMN(),3)=0,B2>0)

Note that the cell reference in this formula always has to match the first cell you selected in step 1
6. Select the desired formatting you want

Second CF Rule
1. Select the range B2:I8
2. Go to Conditional Formatting
3. Click New Rule
4. Select "User a formula to determine which cells to format"
5. Enter the following formula:

=AND(MOD(COLUMN(),3)=2,C2>0)

Note that the cell reference in this formula always has to be one column to the right of the first cell you selected in step 1
6. Select the desired formatting you want

This should do what you want.

 

[JoshTheTofu]

Thank you so much Joe,

This is super helpful.

I am a little embarrassed that I forgot to use the AND function in the conditional formatting like you have done.

I broke the formula down and tweaked it a little bit and it is perfect.

I have no idea how you can just come up with these formulas nd ideas so quickly.

Thank you

Kind regards

Josh

 

[Joe4]

I have no idea how you can just come up with these formulas nd ideas so quickly.

Experience, more than anything!
If you have any particular questions about any part of what I did, please do not hesitate to ask.

Glad I was able to help.