Format cell based on another (macro) Closed

Question

Ok here's a real puzzle.

I need to format cells in column K based on the letter in Column N.
There are only 3 conditions, but it might be made in to 4 or more later (!).
So conditional formatting is only a short term fix...

HOWEVER
When I copy and paste part of the sheet to another sheet I can copy formats but NOT the conditional format as the reference cell is left behind.

So I need a macro which will look basically do:
if N1 = "G" Set K1 Font.ColorIndex = 4
if N1 = "A" Set K1 Font.ColorIndex = 44
if N1 = "R" Set K1 Font.ColorIndex = 3
Else black

And scroll through the different values N1/K1 down to bottom of sheet/last used.

Any ideas?  Tried a LOT of code!
Thanks.

Trowa · Answer

Hi Also,

Insert this code in the sheet you want the code to be applied to:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("N1") = "G" Then Range("K1").Font.ColorIndex = 4
If Range("N1") = "A" Then Range("K1").Font.ColorIndex = 44
If Range("N1") = "R" Then Range("K1").Font.ColorIndex = 3
If Range("N1") <> "G" And Range("N1") <> "A" And Range("N1") <> "R" Then Range("K1").Font.ColorIndex = 1
End Sub

I don't know what you mean by:
quote
And scroll through the different values N1/K1 down to bottom of sheet/last used.
unquote

Hopefully the code will help you on your way.

Best regards,
Trowa

Also · Answer

Thanks.
What I meant was to do it for each row 2 to last row.
In the end I found an inelegant work around- basically hunt in each cell on a loop (so similar as to what I would do using your answer) and then copy and paste formats!
Thanks though- I'll keep it on file as it's taught me how to use THEN more effectively. Now all I need to do is make sure I enter the loop variables into the range properly when I come to use it!

Allan

Dim LastRowP As Long
'This next line defines where the last row is based on the content of the NI type as all should have a type.
LastRowP = Range("O1:O" & Range("O1").End(xlDown).Row).Rows.Count
'This then sets the range to hunt and replace and the three colous.
Set MyPlage = Range("N2" & ":N" & LastRowP)
For Each Cell In MyPlage
If Cell.Value = "G" Then
Cell.Font.ColorIndex = 4
End If
If Cell.Value = "A" Then
Cell.Font.ColorIndex = 44
End If
If Cell.Value = "R" Then
Cell.Font.ColorIndex = 3
End If
Next
'If you want some alts reinstate the below.
' If Cell.Value <> "G" And Cell.Value <> "A" And Cell.Value <> "R" Then
' Cell.Font.ColorIndex = 1
' End If
'Then we copy and paste the format...
Range("N:N").Select
Selection.Copy
Range("K:K").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

Format cell based on another (macro)

2 responses

Similar discussions

Subscribe To Our Newsletter!