Using Conditional Formatting for every third column

JoshTheTofu

New Member
Joined
Oct 15, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
=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

New Member
Joined
Oct 15, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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 ExpectedTotal ActualDifferenceTotal ExpectedTotal ActualDifferenceTotal ExpectedTotal ActualDifference
117-485-385-3
67+166067+1
4539-66462-26463-1
2118-34036-44037-3
106-42431+72423-1
86-265-165-1
67+165-165-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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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:
Excel 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:
Excel 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.
 
Solution

JoshTheTofu

New Member
Joined
Oct 15, 2020
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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.
:)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,569
Messages
5,637,099
Members
416,957
Latest member
Brovashift

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top