Welcome Guest
Log In | Register )
You last visited December 9, 2016, 12:16 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 2 of 2
PrintE-mailLink
Avatar

United States
Member #41846
June 23, 2006
460 Posts
Offline
Posted: March 4, 2009, 8:39 pm - IP Logged

time*treat

I feel that i'm making progress till i hit a wall.  this code does what i want it to.  question is how to terminate and shut it down  you can see my intent was to enter a # larger than 9 as my trigger.  what i'm trying doesnot work and i have been unable to find an example to guide me. thaks for your help and patience.

 


Sub color()
test = True
Call clear_color
Do While test = True
Call set_guess
Call clear_color
Call set_color
If test = False Then Exit Sub
Loop
Exit Sub
End Sub

 

Sub set_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

Public Sub set_guess()
'Public Sub input_box_example()
  'by time*treat'
 
c2 = Application.InputBox("col B", "data", Type:=1)
If c2 > 9 Then GoTo done
c3 = Application.InputBox("col C", "data", Type:=1)
c4 = Application.InputBox("col D", "data", Type:=1)
c5 = Application.InputBox("col E", "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
End Sub

Sub clear_color()
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

    time*treat's avatar - radar

    United States
    Member #13130
    March 30, 2005
    2171 Posts
    Offline
    Posted: March 5, 2009, 6:46 am - IP Logged

    If you have a special reason for using "GoTo done", you will need a line somewhere called "done:" Wink Probably right over an "End Sub" statement.

    Yes, the ":" is required.

    If you aren't going to do some last minute thing before ending the code run, why not use "If c2 > 9 Then End"?

    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 5, 2009, 8:27 am - IP Logged

      If you have a special reason for using "GoTo done", you will need a line somewhere called "done:" Wink Probably right over an "End Sub" statement.

      Yes, the ":" is required.

      If you aren't going to do some last minute thing before ending the code run, why not use "If c2 > 9 Then End"?

      The colon thing is good to know.   the if statement is just too easy.  as rigid as other statement are I would not have tried this.

      its these little things that people leave out or don't mention when trying to show how to do something.  I've looked at several web sites trying to figure things out for myself,  no mention of this.

      thanks and I'll try not to bother you too much.

      p8