Solved

Format same as range cell

Posted on 2013-01-03
11
330 Views
Last Modified: 2013-01-09
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
0
Comment
Question by:Billkronmiller
  • 6
  • 3
  • 2
11 Comments
 
LVL 50

Accepted Solution

by:
teylyn earned 400 total points
ID: 38742163
Hello,

this cannot be done with just formulas and conditional formatting. It would require VBA. Are you comfortable with that?

cheers, teylyn
0
 

Author Comment

by:Billkronmiller
ID: 38742172
I am vaguely familiar with it and have some macros that were done by "Brad" at Experts many years ago. If it is not too complex that may be the way to go.

Thanks Bill
0
 

Author Comment

by:Billkronmiller
ID: 38742197
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
0
 
LVL 50

Expert Comment

by:teylyn
ID: 38742224
You could try this macro:

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

Open in new window


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
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38742226
Bill,

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

Open in new window


Now, your revised approach can be done using Conditional Formatting.  Which version of Excel are you using?
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38742230
Seems teylyn beat me there by a few seconds :)
0
 

Author Comment

by:Billkronmiller
ID: 38742245
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".
0
 

Author Comment

by:Billkronmiller
ID: 38742270
It not over yet. I am heading home and will continue to work there. thanks and will talk in a bit.

Bill
0
 

Author Closing Comment

by:Billkronmiller
ID: 38757578
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
0
 
LVL 50

Expert Comment

by:teylyn
ID: 38757875
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
0
 

Author Comment

by:Billkronmiller
ID: 38758844
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now