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

help with conditional formating in EXCEL

Topic closed. 17 replies. Last post 8 years ago by phileight.

Page 1 of 2
PrintE-mailLink
Avatar

United States
Member #41846
June 23, 2006
460 Posts
Offline
Posted: February 26, 2009, 6:56 pm - IP Logged

A   B  C  D

1

2

3

4

   

 

 

 

1

7

5

5

1

4

3

3

3

2

0

9

5

7

2

1

 

I need some help with conditional formatting. 3 conditions I can do.  I want 4.  Given the  data above  the 1 2 3 4 is the test condition, the other #’s are the data to be tested. I can’t get the row #’s to show up correctly.  If row 3 col A is = to row 1 col A or B or C or D set font color red , If row 3 colB is = to row 1 col A or B or C or D set font color to blue etc. different   color for each col test condition.

Thanks for any help

 

P8

    Avatar
    NASHVILLE, TENN
    United States
    Member #33372
    February 20, 2006
    1044 Posts
    Offline
    Posted: February 28, 2009, 8:30 pm - IP Logged

    try something like this

     

    if cells(row,column) = cells(row,column) then cells(row,column).interior.colorindex = 3

     

    This code must be placed inside a VBA module.  Then you can run the code either from the "Tools-Macro" button from the tool bar or create a run button labeled "Run me" or "Run This" or whatever.

      Avatar

      United States
      Member #41846
      June 23, 2006
      460 Posts
      Offline
      Posted: February 28, 2009, 9:43 pm - IP Logged

      try something like this

       

      if cells(row,column) = cells(row,column) then cells(row,column).interior.colorindex = 3

       

      This code must be placed inside a VBA module.  Then you can run the code either from the "Tools-Macro" button from the tool bar or create a run button labeled "Run me" or "Run This" or whatever.

      Gasmeterguy

      thanks for the help.  I follow the logic and with the syntax i think i can do this part.  how to start the program still eludes me.

      my basic idea is to place my guess in say a1,b1,c1,d1  then use a for next loop for say last 50 games to see the patterns that the numbers in the target comb form in past draws.  the winning number is frequently in a one touch pattern often several times, but difficult to see.

       

      thanks

       

      p8

        Avatar
        NASHVILLE, TENN
        United States
        Member #33372
        February 20, 2006
        1044 Posts
        Offline
        Posted: March 1, 2009, 8:17 pm - IP Logged

        Go to my blog.  I have some information about
        Excel there; how to get to VBA; how to start a program.

        Now I am a rank amatuer when it comes to VBA programming.  There are others on this site that have much more expertise than I.  I know what I want and how to get there.  It ain't pretty but it works and that is all I care about.

        Perhaps a "professional" will step in and guide you.  Until then, I will keep an  eye on this thread.

          Avatar

          United States
          Member #41846
          June 23, 2006
          460 Posts
          Offline
          Posted: March 2, 2009, 9:01 pm - IP Logged

          gasmeterguy

          thanks for the help. i have a working file. a couple of questions if I may.  once i run this various cells are colored in just as i want.  what code returns them to original color or default background.  i can create another loop to clear if i knew what color # to use.  you said create button to start this. I have no clue where to start.  i will include the code i have.   again thanks.


          Public Sub COLOR()

          For X = 4 To 30'  ROW 4 THRU 30
          For Y = 2 To 5 '  COL BCDE

          If Cells(2, 2) = Cells(X, Y) Then Cells(X, Y).Interior.ColorIndex = 3
          If Cells(2, 3) = Cells(X, Y) Then Cells(X, Y).Interior.ColorIndex = 4
          If Cells(2, 4) = Cells(X, Y) Then Cells(X, Y).Interior.ColorIndex = 5
          If Cells(2, 5) = Cells(X, Y) Then Cells(X, Y).Interior.ColorIndex = 6

          Next Y
          Next X

          End Sub

            time*treat's avatar - radar

            United States
            Member #13130
            March 30, 2005
            2171 Posts
            Offline
            Posted: March 3, 2009, 1:13 am - IP Logged

            Interior.ColorIndex = 0

            In neo-conned Amerika, bank robs you.
            Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

              Avatar

              United States
              Member #41846
              June 23, 2006
              460 Posts
              Offline
              Posted: March 3, 2009, 9:06 am - IP Logged

              Interior.ColorIndex = 0

              time*treat

              thanks. That was one of those duh moments, hour or so  after i posted the question it occured to me to use a loop with incremented # to find the answer, something i've done for years in gwbasic.

              I could use help with syntax for a user input.  the equivalent to gwbasic    input" whatever ";x$

              computer provides a prompt and waits for you to hit enter key. 

              even simple things are difficult when you don't understand them.

              thanks

              p8

                time*treat's avatar - radar

                United States
                Member #13130
                March 30, 2005
                2171 Posts
                Offline
                Posted: March 3, 2009, 12:01 pm - IP Logged

                Sub input_box_example()
                  'by time*treat'
                 
                  Dim var_name As Integer
                 
                  var_name = Application.InputBox("Prompt goes here", "box title goes here", Type:=1)

                  'type = 1, for integers'

                  Cells(1, 1).Value = var_name
                End Sub

                Using f8 key to step throuth this one will show you how it works. The "type:=1" statement adds a bit of idiot-proofing so that if you enter a letter then it will prompt you to try again. If you enter 123, then the value of var_name becomes 123.

                In neo-conned Amerika, bank robs you.
                Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

                  RJOh's avatar - chipmunk
                  mid-Ohio
                  United States
                  Member #9
                  March 24, 2001
                  19831 Posts
                  Offline
                  Posted: March 3, 2009, 12:12 pm - IP Logged

                  time*treat

                  thanks. That was one of those duh moments, hour or so  after i posted the question it occured to me to use a loop with incremented # to find the answer, something i've done for years in gwbasic.

                  I could use help with syntax for a user input.  the equivalent to gwbasic    input" whatever ";x$

                  computer provides a prompt and waits for you to hit enter key. 

                  even simple things are difficult when you don't understand them.

                  thanks

                  p8

                  If you understand GWBasic, why not use it to write an application to do what you want?  I've written all my lottery applications using it and complied them using a Quick Basic complier which allows the programs to be larger.

                   * you don't need to buy more tickets, just buy a winning ticket * 
                     
                               Evil Looking       

                    Avatar

                    United States
                    Member #41846
                    June 23, 2006
                    460 Posts
                    Offline
                    Posted: March 3, 2009, 5:45 pm - IP Logged

                    time treat thanks  i believe another light just came on.  i'm sure I will have more questions as I progress.

                     

                    RJOH  I enjoy Gwbasic and would prefer to continue using it.  XP and Vista barely support it.  I have found i have to use Gwbasic to create output files that I then inport to excel so I can see them.  I have bought a couple of books on VB  just have never been able(maybe willing) to get started.  this year my goal is to learn enough VB VBA to convert what I consider my useful GWBasic files to VBA.

                    thanks again for the help

                    p8

                      RJOh's avatar - chipmunk
                      mid-Ohio
                      United States
                      Member #9
                      March 24, 2001
                      19831 Posts
                      Offline
                      Posted: March 3, 2009, 6:29 pm - IP Logged

                      time treat thanks  i believe another light just came on.  i'm sure I will have more questions as I progress.

                       

                      RJOH  I enjoy Gwbasic and would prefer to continue using it.  XP and Vista barely support it.  I have found i have to use Gwbasic to create output files that I then inport to excel so I can see them.  I have bought a couple of books on VB  just have never been able(maybe willing) to get started.  this year my goal is to learn enough VB VBA to convert what I consider my useful GWBasic files to VBA.

                      thanks again for the help

                      p8

                      I too bought Quick Basic and Visual Basic intending to learn them and never learned either well enough to write a program.  It has just been too easy to keep using GWBasic since newer versions of Window had a command window that supported DOS type programs.  I never moved up to Vista but I haven't had any trouble running my programs with XP and I hope Window 7 will do the same as I don't plan to upgrade my computer until the next version of Windows comes out.

                      I'm also surprised to see the latest versions of commercial lottery programs from Gail Howard , Versa Bet and others are still DOS type programs that run in the command window.

                       * you don't need to buy more tickets, just buy a winning ticket * 
                         
                                   Evil Looking       

                        Avatar

                        United States
                        Member #41846
                        June 23, 2006
                        460 Posts
                        Offline
                        Posted: March 3, 2009, 9:06 pm - IP Logged

                        RJOH

                        perhaps it's lack of knowledge on my part but the computers i have tried with xp and vista would not support the lower resolution needed to view gwbasic.  the display is so poor i can barely read it.  it is forceing me to move on before my old computer dies.  maybe thats a good thing.  learning something new keeps the brain cells working, as I get older they need all the exercise I can give them.

                        time*treat or anyone who would like to jump in, i used okay modified your input code and i have a semi working program macro whatever it it would be called. this is my code so far:

                        Public Sub main()

                        For X = 4 To 30 '  ROW 4 THRU 30
                        For Y = 2 To 5 '  COL BCDE

                        If Cells(2, 2) = Cells(X, Y) Then Cells(X, Y).Interior.ColorIndex = 3
                        If Cells(2, 3) = Cells(X, Y) Then Cells(X, Y).Interior.ColorIndex = 4
                        If Cells(2, 4) = Cells(X, Y) Then Cells(X, Y).Interior.ColorIndex = 5
                        If Cells(2, 5) = Cells(X, Y) Then Cells(X, Y).Interior.ColorIndex = 6

                        Next Y
                        Next X
                        'Public Sub input_box_example()
                          'by time*treat'
                         
                          Dim var_name As Integer
                         
                        c2 = Application.InputBox("col 2", "data", Type:=1)
                        c3 = Application.InputBox("col 3", "data", Type:=1)
                        c4 = Application.InputBox("col 4", "data", Type:=1)
                        c5 = Application.InputBox("col 5", "data", Type:=1)

                          'type = 1, for integers'

                         Cells(2, 2).Value = c2
                         Cells(2, 3).Value = c3
                         Cells(2, 4).Value = c4
                         Cells(2, 5).Value = c5
                         
                        For X = 4 To 30 '  ROW 4 THRU 30
                        For Y = 2 To 5 '  COL BCDE

                         Cells(X, Y).Interior.ColorIndex = 0
                         Cells(X, Y).Interior.ColorIndex = 0
                         Cells(X, Y).Interior.ColorIndex = 0
                         Cells(X, Y).Interior.ColorIndex = 0

                        Next Y
                        Next X

                        End Sub

                        question  as you can see it takes my initial data in spreadsheet and colors previous data as i wanted,  lets me input the next set of numbers i want to look at, then clears the first colors.

                        how do i get this to loop until i'm done instead of having to restart it each time?

                        how do i call a subroutine from within this code?

                        thanks for any help

                        p8

                          time*treat's avatar - radar

                          United States
                          Member #13130
                          March 30, 2005
                          2171 Posts
                          Offline
                          Posted: March 3, 2009, 10:18 pm - IP Logged

                          "how do i get this to loop until i'm done instead of having to restart it each time?"

                          You'd have to set some condition and check it periodically; usually another loop. How do you know when you're done?

                           

                          "how do i call a subroutine from within this code?"

                          If you are NOT going to pass any info (values) from one to the other ....

                          sub marco()

                            'do stuff'

                            polo

                            'back from polo to do some other stuff'

                          end sub

                           

                          sub polo()

                            'arrive from marco to do stuff over here'

                          end sub

                          ... otherwise, it's more complex.

                          In neo-conned Amerika, bank robs you.
                          Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.

                            Avatar

                            United States
                            Member #41846
                            June 23, 2006
                            460 Posts
                            Offline
                            Posted: March 4, 2009, 6:50 am - IP Logged

                            I did try this and a dozen variations yesterday, i did have another issue going on  code break finally figured that out using the help file.

                            thanks for your help. for now keeping it simple is where i need to be untill i understand the basics.

                            p8

                              time*treat's avatar - radar

                              United States
                              Member #13130
                              March 30, 2005
                              2171 Posts
                              Offline
                              Posted: March 4, 2009, 7:31 am - IP Logged

                              I too bought Quick Basic and Visual Basic intending to learn them and never learned either well enough to write a program.  It has just been too easy to keep using GWBasic since newer versions of Window had a command window that supported DOS type programs.  I never moved up to Vista but I haven't had any trouble running my programs with XP and I hope Window 7 will do the same as I don't plan to upgrade my computer until the next version of Windows comes out.

                              I'm also surprised to see the latest versions of commercial lottery programs from Gail Howard , Versa Bet and others are still DOS type programs that run in the command window.

                              The inertia of using what I already know has kept me from learning newer languages, even though I have bought books on them.

                              The old code still works. Even though I'm using a newer version of Excel, I'm still using VBA books I bought several years ago.

                              In neo-conned Amerika, bank robs you.
                              Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.