Related:
- Format cell based on another (macro)
- Excel date format dd.mm.yyyy - Guide
- Format factory - Download - Other
- Excel macro to create new sheet based on value in cells - Guide
- Samsung format code - Guide
- Spell number in excel without macro - Guide
2 responses
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
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
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
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
