Search : in
By :

Format cell based on another (macro)

Last answer on Nov 2, 2009 1:13:07 pm GMT Also, on Oct 30, 2009 5:33:35 pm GMT 
 Report this message to moderators

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.

Best answers for « Format cell based on another (macro) » in :
Colouring cells on conditions Show Colouring cells on conditions There are many pratical functions under Excel which is not commonly used. Example: If you wish a cell automatically turns red (or other formatting border, frame etc) under one condition: a result, a...
Excel 2003 VB Macro for finding txt strings Show
Coloring the header row based on the cell col Show
[VBA] Detecting changes in cell Show[VBA] Detecting changes in cell The Event Change feature of a sheet will detects the change in the active cell but it gives no information about the content. The example given below will help you to find out if the cell was changed,...
Inserting an Hyperlink in an Excel Worksheet ShowCreating an Hyperlink in an Excel Worksheet Introduction Implementation Linking to a Website Linking to a document Linking to another cell in the spreadsheet Notes Introduction Excel supports the use of hyperlinks and it is a very...
[Excel]Storage name and path of a spreadsheet Show[ExcelStorage name and path of a spreadsheet The tip below will show you how to automatically show the storage path and the name of any spreadsheet Enter in a cell (E.g cellA1) the following formula: =cell("filename", A1)...
Spreadsheet - Formulas ShowIntroduction to Formulas The main use of a spreadsheet is to automate calculations, which means using cells to perform operations based on values in other cells. The spreadsheet recalculates all the values each time a change is made to the value of...
Ask your question Reply

1

Trowa, on Nov 2, 2009 12:57:04 pm GMT

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

   
Reply to Trowa

2

 Also, on Nov 2, 2009 1:13:07 pm GMT

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

   
Reply to Also
Ask your question Reply
They need your help