Change formats based on A:A cell value on each row - (not with conditional format!) ?

[Marie J-son]

Hi,

I need VBA code for this, I can't use the conditional format function of
some reasons.

Let ? = a row between 6 and 96 and cells in same row between column C:G
should have different fonts, colorindex etc depending on what letter there
is in the cell in A column.

If A? = "K" then cells Range("C?:G?").ColorIndex = 1
If A? = "I" then cells Range("C?:G?").ColorIndex = 2
If A? = "R" then cells Range("C?:G?").ColorIndex = 3

Any suggestions?

By the way, the fastest way possible please, the VBA code will be into
worksheet_change. Maybe use "Find" rather than a Loop, maybe?


/Regards

 

[Tom Ogilvy]

for i = 6 to 96

If Range("A6")(i).value = "K" then cells
Range("C5:G5").Offset(i,0).ColorIndex = 1
If Range("A6")(i).value = "I" then cells
Range("C5:G5").Offset(i,0).ColorIndex = 2
If Range("A6")(i).value = "R" then cells
Range("C5:G5").Offset(i,0).ColorIndex = 3

Next

 

[Bob Phillips]

Why can't you use CF?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Marie J-son]

Because I actually already use CF as one of the formats to be
restored...This VBA code is used with worksheet_change to restore formats in
case of drag and drop etc., where also CF will be overwritten.

Actually, Worksheet_Change is not compleatly sufficient either. There are
events that doesn't trig with WS_change and I'm not fully in control if
there had to be more codes needed to cover other events.


Open question: Are there any other events to be covered? We are talking
aboute ensure that the cell formats will continue to "look right":

I delete comments, verification.delete and all boxes, fonts and boardes you
can record when you change Format-Cells; Format Cells window and change or
delete Conditional Formats.

/Regards

 

[Peter T]

Because I actually already use CF as one of the formats to be

restored...This VBA code is used with worksheet_change to restore formats in
case of drag and drop etc., where also CF will be overwritten.

So why not replace your original CF's

Sub Test()
On Error GoTo errH
Application.EnableEvents = False
With ActiveSheet.Range("c6")
.Activate '
With .FormatConditions
.Delete
.Add xlExpression, , "=$A6=""K"""
.Item(1).Interior.ColorIndex = 3 'red
.Add xlExpression, , "=$A6=""I"""
.Item(2).Interior.ColorIndex = 4 'green
.Add xlExpression, , "=$A6=""R"""
.Item(3).Interior.ColorIndex = 5 'blue
End With
.Copy
.Resize(91, 5).PasteSpecial xlPasteFormats ' c6:g96
End With
errH:
Application.EnableEvents = True
End Sub

If your old CF's might have been dragged elsewhere, maybe you want to delete
all CF's in the sheet at the head of the code.

ActiveSheet.UsedRange.FormatConditions.Delete

Running the above should be faster than looping and changing formats.
However if your values K, I & R in col-A don't change regularly, I would
prefer to format with normal interior colour formats. A bit longer but if
you're running from a macro from time to time perhaps not an issue.

Open question: Are there any other events to be covered? We are talking
aboute ensure that the cell formats will continue to "look right":

Not sure you mean by to be covered, in terms of what?
But you are correct that not all cell changes trigger an event.

Regards,
Peter T