asked on
Format same as range cell
I wish to conditionally format Cells in row A to recognize the cell that matches it in the range of cells in row D and then format it the same as that matching cell in the range in row D.
i.e. A1 would be in bold if D1 were in bold. i.e. A1 would be yellow if D3 were yellow and so on A3 would stay the same as there is no match for it in the list in row D or if there is a match the formatting is the same as the original.
I would like it to only recognize cell color, font color and if the font is bold or not. but this is not necessary.
A B C D
1 L3x3x1/4 L3x3x1/4
2 L6x6x1/4 L4X4X1/4
3 L7x7x1/2 L6X6X1/4
4
Thanks Bill
i.e. A1 would be in bold if D1 were in bold. i.e. A1 would be yellow if D3 were yellow and so on A3 would stay the same as there is no match for it in the list in row D or if there is a match the formatting is the same as the original.
I would like it to only recognize cell color, font color and if the font is bold or not. but this is not necessary.
A B C D
1 L3x3x1/4 L3x3x1/4
2 L6x6x1/4 L4X4X1/4
3 L7x7x1/2 L6X6X1/4
4
Thanks Bill
Microsoft Excel
Last Comment
Billkronmiller
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I could simply place a nuber in an adjacent cell and use thta to trigger the formating. Saw a 1 in row c would be a yellow highlight and a 2 would be bold. That would be much simpler.
what I really want to do is somehow do something to the cell in the range to make it recognizable and I thought formatting that cell would work but i guess not.
A B C D
1 L3x3x1/4 2 L3x3x1/4
2 L6x6x1/4 L4X4X1/4
3 L7x7x1/2 1 L6X6X1/4
4
what I really want to do is somehow do something to the cell in the range to make it recognizable and I thought formatting that cell would work but i guess not.
A B C D
1 L3x3x1/4 2 L3x3x1/4
2 L6x6x1/4 L4X4X1/4
3 L7x7x1/2 1 L6X6X1/4
4
You could try this macro:
Copy the code above
Right-click on the sheet tab and select "View Code"
Paste the code into the big white code window.
Your idea to assign codes to determine the format would also work. You could then use conditional formatting with formulas.
Select all cells in column A, starting with cell A1, then click the Home Ribbon > Conditional formatting > New Rule > Use a formula to determine ... and enter a formula along these lines
=$C1=1
Apply a yellow format
Then create another rule with this formula
=$C1=2
Apply a bold format
and so on.
cheers, teylyn
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tRow As Long
If Not Intersect(Target, Range("A:A")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
tRow = WorksheetFunction.Match(Target, Range("D:D"), 0)
Range("D" & tRow).Copy
Target.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Application.EnableEvents = True
End If
Copy the code above
Right-click on the sheet tab and select "View Code"
Paste the code into the big white code window.
Your idea to assign codes to determine the format would also work. You could then use conditional formatting with formulas.
Select all cells in column A, starting with cell A1, then click the Home Ribbon > Conditional formatting > New Rule > Use a formula to determine ... and enter a formula along these lines
=$C1=1
Apply a yellow format
Then create another rule with this formula
=$C1=2
Apply a bold format
and so on.
cheers, teylyn
Bill,
To make it work as your originally described will require VBA. This seems to be working for me:
Now, your revised approach can be done using Conditional Formatting. Which version of Excel are you using?
To make it work as your originally described will require VBA. This seems to be working for me:
Sub ApplyFormats()
Dim LastRColA As Long
Dim LastRColD As Long
Dim MatchRng As Range
Dim Counter As Long
Dim CopyFromRng As Range
Dim CopyToRng As Range
With ThisWorkbook.Worksheets("Sheet1")
LastRColA = .Cells(.Rows.Count, "a").End(xlUp).Row
LastRColD = .Cells(.Rows.Count, "d").End(xlUp).Row
Set MatchRng = .Range("a1:a" & LastRColA)
With .Range("d1:d" & LastRColD)
.Interior.ColorIndex = xlColorIndexNone
.Font.ColorIndex = xlAutomatic
.Font.Bold = False
End With
For Counter = 1 To LastRColD
Set CopyToRng = .Cells(Counter, "d")
If CopyToRng <> 0 Then
On Error Resume Next
Set CopyFromRng = .Cells(Application.Match(CopyToRng.Value, MatchRng, 0), "a")
If Err = 0 Then
CopyFromRng.Copy
CopyToRng.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Else
Err.Clear
End If
On Error GoTo 0
End If
Next
End With
MsgBox "Done"
End Sub
Now, your revised approach can be done using Conditional Formatting. Which version of Excel are you using?
Seems teylyn beat me there by a few seconds :)
ASKER
I have excel 2010. However the actual range of cells I am using are instead of D1 it is "Data!E42:E3000" and the range of cells in column is actually "TAKE OFF!$H9:$H3000".
ASKER
It not over yet. I am heading home and will continue to work there. thanks and will talk in a bit.
Bill
Bill
ASKER
There really wasn't an answer as excel does not recognize formatting of cells. Hopefully it will in future releases. It would be very useful and save lots of formula writing and look-ups.
thanks Bill
thanks Bill
Hello,
there was an answer. In fact there were three:
- do it with conditional formatting with a helper cell and a code number for each format
- do it with the code matthewspatrick posted
- do it with the code I posted.
If you don't want to use VBA or the helper cell approach, then "it can't be done" is a valid answer and deserves an "A" grade, not a "B". Experts here are not responsible for the way Excel works. I appreciate that you are not happy with the way Excel works and I have similar gripes with Excel and other software. Still, if it can't be done and an Expert tells you so, it is an "A" grade.
regards, teylyn
there was an answer. In fact there were three:
- do it with conditional formatting with a helper cell and a code number for each format
- do it with the code matthewspatrick posted
- do it with the code I posted.
If you don't want to use VBA or the helper cell approach, then "it can't be done" is a valid answer and deserves an "A" grade, not a "B". Experts here are not responsible for the way Excel works. I appreciate that you are not happy with the way Excel works and I have similar gripes with Excel and other software. Still, if it can't be done and an Expert tells you so, it is an "A" grade.
regards, teylyn
ASKER
Teylyn, I do apologize for the B and now that you have explained it to me I would be happy to change the grade to an A. I do find VBA to be a little cumbersome and with multiple users and constantly changing my spread sheet i would just as soon not use it. I do appreciate you input and please let me know what i can do to change your grade and in the future i will keep this in mind.
Thanks again, Bill
Thanks again, Bill
Thanks Bill