Slightly tricky conditional formatting

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,707
Office Version
2016
Platform
Windows
I have this code I want it to do a job for me but it's not finishing it properly.

Code:
Dim eCell As Range
For Each eCell In [E5:E18]
          eCell.Offset(eCell.Row-5, -1).Font.Color = eCell.Font.Color
           eCell.Offset(eCell.Row-5, -3).Font.Color = eCell.Font.Color
Next eCell
The aim is to make the font colors of the row from col B to Col D same as the the font color in E. Meanwhile col C is excluded.

I am using a conditional formatting for col E.

I am in for a better way to get this done.

Thanks
 

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
206
Office Version
365, 2013
Platform
Windows
If you wants to 'recopy' color of cell which is results of Conditional Formatting is not a way.
It could works (not tested) for hardcoded color.
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,707
Office Version
2016
Platform
Windows
Okay

So I decide to avoid the conditional formatting and used a code to set the font colors.

It's working now just that I am having issues with the offset.

The font color in col E does not match the B and D.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,882
Office Version
365
Platform
Windows
Based on your OP using CF, how about
Code:
Dim eCell As Range
For Each eCell In [E5:E18]
          eCell.Offset(, -3).Font.Color = eCell.DisplayFormat.Font.Color
         eCell.Offset(, -1).Font.Color = eCell.DisplayFormat.Font.Color
Next eCell
But wouldn't it be easier to do it conditional formatting?
 
Last edited:

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,707
Office Version
2016
Platform
Windows
@Fluff, it worked great!!!

As with the conditional formatting I am stacked. you can point me to that direction so I decide which will be sweet for me.:cool:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,882
Office Version
365
Platform
Windows
What formula are you using for the CF?
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,707
Office Version
2016
Platform
Windows
I used
="$E5<>YES"
while selecting the various columns
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,882
Office Version
365
Platform
Windows
If you select B5:B18 and D5:E18 then use
=$E5="YES"
that should format all three columns if col E=YES
 

Forum statistics

Threads
1,085,882
Messages
5,386,544
Members
402,004
Latest member
moneyman4438

Some videos you may like

This Week's Hot Topics

Top