Welcome Guest
Log In | Register )
You last visited December 9, 2016, 12:40 am
All times shown are
Eastern Time (GMT-5:00)

excel help

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

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

If Range("A3").Select = 0 Then Selection.Font.ColorIndex = 3 Else Selection.Font.ColorIndex = 5

I then tried to step into it andadd the above.  Now sheet 2 (A2) turnes to grey.  I guess I am getting closer to something

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

    OK.  Scratch everything above!  This is kind of what I want to do except it changes every grey cells color & I only want it to change those cells that have 0 in them.

    How can I do this?

        Selection.Interior.ColorIndex = 40
        Range("B:B,D:D,F:F,H:H,J:J,L:L,N:N").Select
        Range("N1").Activate
        Cells.Find(What:="0", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=True).Activate
        Selection.Interior.ColorIndex = 40
        Cells.FindNext(After:=ActiveCell).Activate
        Selection.Interior.ColorIndex = 40
        Cells.FindNext(After:=ActiveCell).Activate
        Selection.Interior.ColorIndex = 40
        Cells.FindNext(After:=ActiveCell).Activate
        Selection.Interior.ColorIndex = 40
        Cells.FindNext(After:=ActiveCell).Activate
        Selection.Interior.ColorIndex = 40
        Cells.FindNext(After:=ActiveCell).Activate
        Selection.Interior.ColorIndex = 40
        Cells.FindNext(After:=ActiveCell).Activate
        Selection.Interior.ColorIndex = 40
    End Sub

    And Thank You SirMetro & wveryone else.  You were right.  I was able to figure some things out for myself.  But I am sure that I will not be able to figure that out.  But sometimes I surprise my self  :))

      Todd's avatar - Cylon 2.gif
      Chief Bottle Washer
      New Jersey
      United States
      Member #1
      May 31, 2000
      23273 Posts
      Online
      Posted: June 6, 2005, 8:25 am - IP Logged

      lottaloot,

      If all you're trying to do is to color a cell when it is a particular value (or in a range of values), then using a macro is not necessary.

      Let me preface this by saying that I use Excel 2003, so I cannot be sure this feature is in your version (if you use an earlier version), but it should be there.

      There is something called "conditional formatting".  It is rather easy to use.

      I will give you a step-by-step how to use it, and then I'm sure you can play around and exploit some of the real power it offers.  In my test here, I will highlight cells with a zero (0) in them.

      1. Open whatever spreadsheet that contains at least some zeros, or just start a new spreadsheet and enter some random numbers in cells, being sure to have at least one with a zero.
      2. Highlight the area that you want to apply the conditional formatting to, or else just highlight the whole spreadsheet by clicking the empty square above the "row 1" marker and to the left of the "column a" marker.
      3. In the format menu, select Conditional Formatting.
      4. Set Condition 1 to "Cell Value Is", change the next option to "equal to", and type number 0 in the box.
      5. Click the Format button, which will tell Excel how to format cells containing zeros.  To make the cell red, click the Patterns tab, then click the color you want, then OK.
      6. Click OK to close the Conditional Formatting box, and that should do it.

      Like I said, this technique is very powerful, and you can create many conditional formats anywhere in your spreadsheet, even combining conditions.

       

      Check the State Lottery Report Card
      What grade did your lottery earn?

       

      Sign the Petition for True Lottery Drawings
      Help eliminate computerized drawings!

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

        Lottaloot, you can actually cut and paste everything below. Use function key F8 to step thru to watch what it does. The comments will turn to green after you finish the paste process. Good Luck.

        Sir Metro


        Sub TestForZero()
        i = 0 'Initialize i to 0
        'Create a Loop, set it to be whatever range you want to test for
        For i = 1 To 100
          'Create a point of reference (Trim statement pulls the unwanted spaces from the number)
            xCellLocation = "N" + Trim(Str(i))
          'Select the intended point of reference
            Range(xCellLocation).Select
          'Assign the cell contents to a variable
            xCellValue = Range(xCellLocation).Value
          'Break the cells contents into 3 parts
            xCellA = Left((Trim(Str(xCellValue))), 1)  'Gets the first digit
            xCellB = Mid((Trim(Str(xCellValue))), 2, 1) 'Gets the second digit
            xCellC = Right((Trim(Str(xCellValue))), 1)  'gets the 3rd digit
          'The test below assumes the defined cell contains 1 digit
            If xCellA = "0" Then 'See if the first position is a 0
                    With Selection.Interior        'Yes, do this
                        .ColorIndex = 7
                        .Pattern = xlSolid
                    End With
            ElseIf xCellB = "0" Then              'if not, test 2nd position
                    With Selection.Interior        'Yes, do this
                        .ColorIndex = 7
                        .Pattern = xlSolid
                    End With
            ElseIf xCellC = "0" Then            'if not, test 3rd position
                        With Selection.Interior    'Yes, do this
                            .ColorIndex = 7
                            .Pattern = xlSolid
                        End With
            Else                       'And if all 3 is not 0, then do this
                        With Selection.Interior
                            .ColorIndex = 3
                            .Pattern = xlSolid
                        End With
            End If

        Next

        End Sub

         

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

          Todd, I have 2003 excel also. 

          SirMetro, Thanks again. 

          And to everyone else who assisted in this thread--"You guys are the best"

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

            they are the best, aren't they, Lottaloot?

            And again, thank you guys for dummies like me for helping me out in the past and in the present.

            Sandy

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

              Ding, Ding, Ding!!!

              After checking out this book on VBA "4" times, I get what a macro is.

              It's just an easy way to do the same repetitive, tedious actions over & over & over again.

              And now I can step in & out of macro like it "aint nothin"

              I thought that when I recorded a macro, I had to go fast so I would'nt run out of "tape", I guess Roll Eyes

              but now I realize, it's just like dictation.  It copies down what you want to do to your spreadsheet & plays it over & over at the click of a button. 

              Thanks Sir Metro, for making me strain my "itty bitty little brain" in order to figure that out.Big Grin 

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

                LOL...around 15 or so years ago...the same ding ding ding happened to me with Lotus 123. I had gotten so frustrated not being able to figure out Lotus, I actually wrote a program to do what the spreadsheet was suppose to do. Today, I do everything in Excel...from breif notes to complex quotes and billing documents. With time comes experience. Now that the bells have sounded, your limits is your imagination...I look forward to your great results.

                Sir Metro

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

                  PS...after you record your macro...you will learn how to clean it up. Also, highlight the formulas and hit  F1 to see what it really means. Thats sometimes the best way to learn the system.

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

                    Simple question but I can't make it work for me. 

                    Trying to get excel to calculate the sum of a number into its root. 

                     

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

                      Lottaloot,

                      If I understand your question correctly, I think you are trying to determine root sum.

                      369 = sum 18 = root 9

                      in Excel, the formula would look like this (assuming the 369 is in cell B11)

                      =IF((SUM((LEFT(B11,1)*1)+(MID(B11,2,1)*1)+(RIGHT(B11,1)*1)))>9,(SUM((LEFT((SUM((LEFT(B11,1)*1)+(MID(B11,2,1)*1)+(RIGHT(B11,1)*1))),1)*1)+(RIGHT((SUM((LEFT(B11,1)*1)+(MID(B11,2,1)*1)+(RIGHT(B11,1)*1))),1)*1))),(SUM((LEFT(B11,1)*1)+(MID(B11,2,1)*1)+(RIGHT(B11,1)*1))))

                      If the above formula looks a bit long, that is because it really is long. Don't forget to change the B11 to whatever your actuall cell reference is.

                      The (*1) is a simple way of forcing a string to a number to make sure you do not get a run-time error. BTW, this formula actually works.

                      Sir Metro

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

                        Thanks

                        That's works just fine. 

                         

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

                          oh....I forgot to mention one itsybitsy little item.

                          if the number is 023 or 005, you MUST enter into your spreadsheet cell as '023 or '005

                          The reason for this is because Excel automatically trims the leading 0 out, something I am sure you have noticed. The apostraphe works just fine and you will get the result you are interested in.

                          Sir Metro

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

                            Got that part; however, it is not recognizing doubles 

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

                              Got that part; however, it is not recognizing doubles 

                              Please ignore the above. 

                              The problem is not with the formula, it is with my technigue. 

                              On my all states spreadsheet it works fine--it is on the MN sheet that has the problem.

                              I will retrack with I'm doing wrong. 

                               

                              Thanks SirMetro