VBA If Then Else on a Range

nitrammada

New Member
Joined
Oct 10, 2018
Messages
8
Hi Everyone,
I'm looking for code that does the following and would appreciate any help you may have.
My table has 24 rows of data, column A I have 6 heading types, H1 through to H6.
Each row has its own heading type.
I'm looking for code that will search column A, when it finds the particular heading type, do the following:

H1 - leave the font colour black
H5 - change the font colour to red
H3 - change the font colour to pink
H4 - change the font colour to cyan
H5 - change the font colour to green
H6 - leave the font colour black

Below is a snap shot of my data set. I have set this up as a sample to explain what I'm after.
1612057284296.png


If I can find code that works I will apply to my actual database that 1000's of rows.
But for now if I can understand how the code works it would be a great help.
Any assistance would be greatly appreciated.
Thanks in advance.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

aRandomHelper

Board Regular
Joined
Jan 14, 2021
Messages
225
Office Version
  1. 2016
Platform
  1. Windows
Can be achieved with conditional formatting:
a.xlsx
ABCDEF
4H1abcde
5H2abcde
6H3abcde
7H4abcde
8H5abcde
9H6abcde
10H1abcde
11H2abcde
12H3abcde
13H4abcde
14H5abcde
15H6abcde
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A4:F15Expression=$A4="H6"textNO
A4:F15Expression=$A4="H5"textNO
A4:F15Expression=$A4="H4"textNO
A4:F15Expression=$A4="H3"textNO
A4:F15Expression=$A4="H2"textNO
A4:F15Expression=$A4="H1"textNO


Just make sure you enter the formula correctly.
 

nitrammada

New Member
Joined
Oct 10, 2018
Messages
8
Hello aRH, thanks so much for responding. I have already used conditional formatting as you mentioned, and it works ok, however, i'm using a pivot table to manipulate my data and when I expand / collapse based on heading type the formatting goes all over the show, hence I thought VBA would be better. Any suggestions?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,588
Messages
5,625,668
Members
416,124
Latest member
DeMoNloK

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