Slightly tricky conditional formatting

[kelly mort]

I have this code I want it to do a job for me but it's not finishing it properly.

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]

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]

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]

Based on your OP using CF, how about

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?

 

[kelly mort]

@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.

 

[Fluff]

What formula are you using for the CF?

 

[kelly mort]

I used
="$E5<>YES"
while selecting the various columns

 

[Fluff]

If you select B5:B18 and D5:E18 then use
=$E5="YES"
that should format all three columns if col E=YES

 

[kelly mort]

Okay I will give it a try