Welcome Guest
Log In | Register )
You last visited December 4, 2016, 3:15 pm
All times shown are
Eastern Time (GMT-5:00)

excel help

Topic closed. 29 replies. Last post 11 years ago by lottaloot.

Page 1 of 2
PrintE-mailLink
Sandy K's avatar - graphic pub.jpg
Clarkston, Michigan
United States
Member #76
January 6, 2002
8528 Posts
Offline
Posted: June 1, 2005, 2:47 pm - IP Logged

anyone out there know the answer to this question...my husband emailed me this a few minutes ago.......



hey .. ask one of your excel wizards this question ..


I have a workbook with 2 worksheets


in cell AI of sheet 1 format the cell to have a pattern of red


how to you "link"


cell a2 in sheet 2 so that the format is the same as cell a1 in sheet 1


what I am trying to do .. if the color of cell a1 in sheet one is changed to "purple... I want cell a2 in sheet 2 to automatically change to purple.


anyone have any ideas??????????????????

Sandy

    johnph77's avatar - avatar
    CA
    United States
    Member #2987
    December 10, 2003
    832 Posts
    Offline
    Posted: June 1, 2005, 4:51 pm - IP Logged

    Format them both at the same time - select (highlight) them both and format. If the cells are adjacent you can select them as a range and format and reformat using the range options. If they are not adjacent use the "CTRL" key to select both cells. They can be reformatted with the same process.

    There is no way I know of to reformat the second non-adjacent cell automatically upon the reformatting of the first cell.

    gl

    j

    Blessed Saint Leibowitz, keep 'em dreamin' down there..... 

    Next week's convention for Psychics and Prognosticators has been cancelled due to unforeseen circumstances.

     =^.^=

      SirMetro's avatar - center
      East of Atlanta
      United States
      Member #6191
      August 11, 2004
      1389 Posts
      Offline
      Posted: June 1, 2005, 5:33 pm - IP Logged

      Below is the basic code for what he is asking it the program to do.

      Take it, apply a bit of programming thought to it, link it with a button and enjoy....be sure to adjust the BOLD items as needed to copy the data back and forth.

      Sub Macro1()

          Sheets("Sheet1").Select

          Range("A1").Select

          Selection.Copy

          Sheets("Sheet2").Select

          Range("A2").Select

          Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _

              False, Transpose:=False

      End Sub

      There are other methods, that basically establish a test of each cell to see if one matches the other. A bit more complex, but it can be done.

      And the final note, basically, I cheated. Simpliest way to figure out how to make Excel do a formula or a program that will do what you want of it is to "Record a MACRO" and then step thru the process that you know will be required to do the job and then click EDIT Macro and see what the heck Excel needs in order to establish a usuable program. I do it all the time. Painless and almost bug free.

      Sir Metro

        Sandy K's avatar - graphic pub.jpg
        Clarkston, Michigan
        United States
        Member #76
        January 6, 2002
        8528 Posts
        Offline
        Posted: June 2, 2005, 5:30 pm - IP Logged

        thank you, Johnph and Sir Metro.............haven't had a chance to ask him if it worked or not.  I know his employees said it was impossible to do what he wanted to do.........but he wanted me to ask you guys anyway just in case....

        Sandy

          Sandy K's avatar - graphic pub.jpg
          Clarkston, Michigan
          United States
          Member #76
          January 6, 2002
          8528 Posts
          Offline
          Posted: June 2, 2005, 6:56 pm - IP Logged

          Sir Metro,

          I just talked to my husband......the macro worked.

          Thank you.

          I just told him, why does he have people work for him who can't figure that stuff out....and he told me the excel specialist was on vacation....came back today...and told him what you said was the way to go......

          You are the best......again, thank you.

          Sandy

            SirMetro's avatar - center
            East of Atlanta
            United States
            Member #6191
            August 11, 2004
            1389 Posts
            Offline
            Posted: June 4, 2005, 8:24 am - IP Logged

            Glad to be of assistance.

            Sometimes, the simpliest solution is the most difficult to understand.

            Sir Metro

              SirMetro's avatar - center
              East of Atlanta
              United States
              Member #6191
              August 11, 2004
              1389 Posts
              Offline
              Posted: June 4, 2005, 11:20 pm - IP Logged

              Below are 2 more options that can also be used to set colors of either text font or cell color. Hope they are usable. Also, the simple intent of the routines below is to establish a test for a cell. With a bit of massaging and a little creative thought process, you can about do anything. As for color codes, you will have to check the help list to determine the color with the number.

              The First example is to change the font color
              If Range("A3").Select = 0 Then Selection.Font.ColorIndex = 3 Else Selection.Font.ColorIndex = 5

              The Second example is to change the CELL color
              If Range("A3").Select = 0 Then
                      With Selection.Interior
                          .ColorIndex = 7
                          .Pattern = xlSolid
                      End With
              Else
                      With Selection.Interior
                          .ColorIndex = 3
                          .Pattern = xlSolid
                      End With
              End If

              You can be explicit in selecting each cell to test or you can create a routine that will allow you to step thru each cell in a loop.

              Good Luck,

              Sir Metro

                lottaloot's avatar - AvatarZ56
                Redford/MI
                United States
                Member #3396
                January 18, 2004
                4867 Posts
                Offline
                Posted: June 5, 2005, 9:27 am - IP Logged

                Below are 2 more options that can also be used to set colors of either text font or cell color. Hope they are usable. Also, the simple intent of the routines below is to establish a test for a cell. With a bit of massaging and a little creative thought process, you can about do anything. As for color codes, you will have to check the help list to determine the color with the number.

                The First example is to change the font color
                If Range("A3").Select = 0 Then Selection.Font.ColorIndex = 3 Else Selection.Font.ColorIndex = 5

                The Second example is to change the CELL color
                If Range("A3").Select = 0 Then
                        With Selection.Interior
                            .ColorIndex = 7
                            .Pattern = xlSolid
                        End With
                Else
                        With Selection.Interior
                            .ColorIndex = 3
                            .Pattern = xlSolid
                        End With
                End If

                You can be explicit in selecting each cell to test or you can create a routine that will allow you to step thru each cell in a loop.

                Good Luck,

                Sir Metro

                OK excel experts, I need help as well understanding the macro above. 

                SirMetro was kind enough to give me this marco formula as well but I don't know where the heck to place it. 

                Do I go to macro section of excel and add a new macro?

                Do I type the formula into the marco area?

                Do I go under record & put in the above as the macro?

                Basically speaking, I do not know what to do with a macro but I really want to color code certain areas. 

                HELP!!What?

                  lottaloot's avatar - AvatarZ56
                  Redford/MI
                  United States
                  Member #3396
                  January 18, 2004
                  4867 Posts
                  Offline
                  Posted: June 5, 2005, 9:50 am - IP Logged

                  Trying to figure it out. 

                  Went into the visual basic editor and tried to insert If Range("A3").Select = 0 Then Selection.Font.ColorIndex = 3 Else Selection.Font.ColorIndex = 5 into the module but I do not think that this is correct. 

                  HELP!!What?

                    SHTh's avatar - baby

                    United States
                    Member #1959
                    August 1, 2003
                    42 Posts
                    Offline
                    Posted: June 5, 2005, 10:12 am - IP Logged

                    1) Press Alt+F11 to open VBA

                    2) From Insert menu, select Add New Module
                    3) Copy and paste the code

                    4) close and return to Excel

                    5) To run macro:  Press F5 or go via tools menu select macro name in macro list and Run.

                    SHTH

                    9 years hunter, 17 weeks trapper. While the pursuits of hunting and trapping may be contiguous activities the ideology and actions involved are easily distinguishable.

                      SHTh's avatar - baby

                      United States
                      Member #1959
                      August 1, 2003
                      42 Posts
                      Offline
                      Posted: June 5, 2005, 10:25 am - IP Logged

                      Lottaloot

                      With reference to the code you posted, it has missing code at the beginning that tells excel that it is a macro.

                      All macros must start with the code:    Sub MacroName()    example Sub colmatch()

                      All macros must end with:    End Sub
                      Hence copy the code and paste the code between

                      Sub colmatch()
                         Paste your macro code here
                      End Sub

                      Lottaloot you may need to edit the code, so that it specifies what sheets you want to format.

                      Sorry I haven't got time to test the code for you, but goodluck.

                      SHTH

                      9 years hunter, 17 weeks trapper. While the pursuits of hunting and trapping may be contiguous activities the ideology and actions involved are easily distinguishable.

                        lottaloot's avatar - AvatarZ56
                        Redford/MI
                        United States
                        Member #3396
                        January 18, 2004
                        4867 Posts
                        Offline
                        Posted: June 5, 2005, 12:38 pm - IP Logged

                        1) Press Alt+F11 to open VBA

                        2) From Insert menu, select Add New Module
                        3) Copy and paste the code

                        4) close and return to Excel

                        5) To run macro:  Press F5 or go via tools menu select macro name in macro list and Run.

                        SHTH

                        Thanks. 

                        I did that and so far I only see user form, module, & class module as choices here.  Maybe once I get home, I will see add new module on a newer version of excel. 

                        But this does help. 

                        Any other tips are appreciated. 

                          lottaloot's avatar - AvatarZ56
                          Redford/MI
                          United States
                          Member #3396
                          January 18, 2004
                          4867 Posts
                          Offline
                          Posted: June 5, 2005, 3:01 pm - IP Logged

                          submacro()
                          Sheets("sheet1").Select
                          Range("a1").Select
                          Selection.Copy
                          Sheets("sheet2").Select
                          Renage("a2").Select
                          Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                          End Sub

                          OK.  I managed to play around with this one and it let me place the macro into VBA and was told by micro excel that no RETURN() or HALT()function found on the macro. 

                          What does that mean? 

                          Am I getting closer to do it correctly? 

                          Also, do you mean that I can name the macro SubanynameIChoose() i.e, SubColor()

                            SirMetro's avatar - center
                            East of Atlanta
                            United States
                            Member #6191
                            August 11, 2004
                            1389 Posts
                            Offline
                            Posted: June 5, 2005, 7:34 pm - IP Logged

                            the simpliest approach to the entire process is to

                            press CNTRL + F8

                            which will bring up the Macro menu.

                            Click CREATE Macro, it will ask for a name then paste in the previous formulas.

                            Sir Metro

                              lottaloot's avatar - AvatarZ56
                              Redford/MI
                              United States
                              Member #3396
                              January 18, 2004
                              4867 Posts
                              Offline
                              Posted: June 6, 2005, 12:43 am - IP Logged

                              Getting closer.  But when I try and run the macro, a pop-up of Run-time error 9 subscript out of range occurs. 

                              When I hit debug, this is what I see

                              Sub pairbook2()
                              Sheets("doubles").Select

                              Range("a1").Select
                              Selection.Copy
                              Sheets("sheet2").Select
                              Range("a2").Select
                              Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                              End Sub

                              Did it again and it did this

                              Sub pairbook2()
                              Sheets("doubles").Select
                              Range("a1").Select
                              Selection.Copy
                              Sheets("sheet2").Select
                              Range("a2").Select
                              Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                              End Sub

                              So then I added a sheet 2 and it did not return anything more---it took me to A2 on sheet 2

                              Looking within help, I think that it says that I  referenced a nonexistent array element.  Was this the case?

                              And why didn't it do anything?