Code instead of copy n' paste the conditional format by hand

[Guest]

Hi there!

If you know this trick, please help me! I have killed a lot of time...

To be able to keep formats in my B:B row, I use the contitional format
function. When I use a relative formula like =$A6="I" everything work fine
( yellow interior color when "I", Black interior when "K".) . I just copy
the format manually to all cells and the relative row fill out the area.

However, I had to create code for it, running everytime with "Sub
Worksheet_Change". This because I need to keep the formats when i paste text
from other worksheets. If I have code activating when I change information,
I can reformat the cell right again .

Earlier I used a workaround.and disabled copy and paste instead, but you
lose a lot with that...

/Regards

 

[Guest]

Let me be more specific:

I don't want to run the code through all rows every time a change in the
worksheet take place - it will "shake all the" time in that case...I just
want to make a check in the cell to the left for that specific row the
change took place, if that is possible.

I have tried to start from a macro when recording format conditioning in
excel. A part of it look of cource like this:
...
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$A$6:$A$102=""I"""
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 36
End With
.... etc

If I want to run code with Sub Worksheet_Change, I guess I had to have a
compleate other approach than "=$A$6:$A$102=""I""" Right?

/Regards


**************