Conditional Formatting of Cell Ranges

[Pete Wright]

Hello! I'm new to this Forum and this is my first question (actually a couple of questions) which I already asked on the Google Groups Discussions a few days ago, but I don't expect an answer there soon, so I am cross-posting my questions here (sorry for that).

So, here we go:

I have the following example table:
There is a group of 3 columns for each month

    A     B        C        D    E        F        G    H      I    ...
1        Dec-16         |        Jan-17        |      Feb-17
  ----------------------|----------------------|---------------------
2  day | pet |  amount  | day | pet |  amount  | day | pet |  amount
  ----------------------|----------------------|---------------------
3  10. | cat | -10.00 € | 03. | rat |  12.00 € | 12. | cat | -36.00 €
4  19. | pig |   7.00 € | 07. | cat | -45.00 € | 24. | dog |   8.00 €
5  20. | dog | -34.00 € | 25. | dog | -28.00 € | 24. | dog | -26.00 €
...

What I want:

result as on following screenshot:

1) If 2nd cell of a group of 3 columns contains specific text, all three cells should be formatted in a specific color.

Example:

2nd cell = "cat" => background color = red
B3="cat" => A3:C3=<red background>
E4="cat" => D4:F4=<red background>
H3="cat" => G3:I3=<red background>
etc.

2nd cell = "dog" => background color = blue
B5="dog" => A5:C5=<blue background>
E5="dog" => D5:F5=<blue background>
H4="dog" => G4:I4=<blue background>
H5="dog" => G5:I5=<blue background>
etc.


2) The group which shows current month should be formatted in specific color.

Example:

Today is 01/18/2017, so the three merged cells containing "Jan-17" should be formatted. In Addition the three cells below showing the title should be formatted too.
D1=<current month> => D1 (D1:F1)=<yellow background>
D1=<current month> => D2:F2=<yellow background>


Could somebody please explain how to achieve this?



Thanks in advance
Pete

 

[PCosta87]

Hello! I'm new to this Forum and this is my first question (actually a couple of questions) which I already asked on the Google Groups Discussions a few days ago, but I don't expect an answer there soon, so I am cross-posting my questions here (sorry for that).

So, here we go:

I have the following example table:
There is a group of 3 columns for each month

    A     B        C        D    E        F        G    H      I    ...
1        Dec-16         |        Jan-17        |      Feb-17
  ----------------------|----------------------|---------------------
2  day | pet |  amount  | day | pet |  amount  | day | pet |  amount
  ----------------------|----------------------|---------------------
3  10. | cat | -10.00 € | 03. | rat |  12.00 € | 12. | cat | -36.00 €
4  19. | pig |   7.00 € | 07. | cat | -45.00 € | 24. | dog |   8.00 €
5  20. | dog | -34.00 € | 25. | dog | -28.00 € | 24. | dog | -26.00 €
...

What I want:

result as on following screenshot:

1) If 2nd cell of a group of 3 columns contains specific text, all three cells should be formatted in a specific color.

Example:

2nd cell = "cat" => background color = red
B3="cat" => A3:C3=<red background>
E4="cat" => D4:F4=<red background>
H3="cat" => G3:I3=<red background>
etc.

2nd cell = "dog" => background color = blue
B5="dog" => A5:C5=<blue background>
E5="dog" => D5:F5=<blue background>
H4="dog" => G4:I4=<blue background>
H5="dog" => G5:I5=<blue background>
etc.


2) The group which shows current month should be formatted in specific color.

Example:

Today is 01/18/2017, so the three merged cells containing "Jan-17" should be formatted. In Addition the three cells below showing the title should be formatted too.
D1=<current month> => D1 (D1:F1)=<yellow background>
D1=<current month> => D2:F2=<yellow background>


Could somebody please explain how to achieve this?



Thanks in advance
Pete

Hi,

First of all, welcome to the forum

Secondly, here you go... please refer to attachment

Hope it helps.

 

[Gireesh]

Hi Pete,


Select range A3:C5, -->Conditional Formatting -->New Rule -->'Use a formula' -->ok -->apply

Do the same process for Jan-17 (D3:F5) & Feb-17

Regards,
GK

 

[Pete Wright]

Thank you both for the quick reply!

Hi,
First of all, welcome to the forum
Secondly, here you go... please refer to attachment
Hope it helps.

You have plenty of formatting rules in your sheet, maybe it can be reduced by selecting larger cell ranges instead of a single cell for each rule.

Hi Pete,
View attachment 37744
Select range A3:C5, -->Conditional Formatting -->New Rule -->'Use a formula' -->ok -->apply
Do the same process for Jan-17 (D3:F5) & Feb-17
Regards,
GK

I like your version

I think I can work with that...

Although both require a lot of rules to be set up and "cat" and "dog" are just two of a huge pile. Ten to be exact.
Actually it's a workbook for my finances, the animals are just for explanation.

For example I have two jobs and the cell group with "employer A" and "employer B" should have the same color. This can be done with:
=OR($B3="employer A",$B3="employer B")

Then I have some things like "rent 12/2016", "rent 01/2017", aso.
This can be done with:
=LEFT($B3,4)="rent"

Summing up I'll have 10*12 rules for the coloring and 2*12 for highlighting the current month. That'll make 144 per worksheet/year. Phew!

Is there any way to reduce the rules (especially the ones for month highlighting)?

 

[PCosta87]

Hi,

I didn't select larger ranges in order for you to be able to just copy/paste formats to future columns... with @Gireesh's solution, you have to manually set the conditional formatting for each new set of columns.

As for reducing the amount of rules for the month highlighting, you can select the entire first row and apply the rule which will leave you with just one rule for the entire row... now, for the second row, since it will refer to the first one, the problem is that you are merging cells which complicates things... if possible, it would be better to have another row up top (not merged) to store the month/year and have the rules account for those instead of the merged ones.

On a side note, with some simple lines of code you could avoid the conditional formatting altogether and have VBA do that work for you.

Hope this helps