Format the correct cell with vba

Big Lar · Aug 28, 2015

This code gets close to doing what I require.
Please suggest the correct syntax:

I’m attempting to change the Interior color of my test: Sheets(“GROSS”).[C31]

When cName is “Robert”
Sheets(“PAYOUTS”).[T4] = “Robert”
And Sheets(“Gross”).[ A31] = “Robert”

Rich (BB code):

  Sub ProxyTest()
      With Sheets("PAYOUTS")
      Sheets("PAYOUTS").Select
   
  For i = 3 To 50
  p = Sheets("PAYOUTS").Range("S4").Value + 1
  p = "C"
  cName = Sheets("PAYOUTS").Range("T4").Value
   
      If Range("T" & i).Value = cName Then
       ThisWorkbook.Worksheets("GROSS").Range(p & i).Interior.Color = 255

            
  End If
  Next i

  End With
  End Sub

dreid1011 · Aug 28, 2015

I believe you want .ColorIndex =

What color are you trying to use?

Big Lar · Aug 28, 2015

Thanks dreid1011, and sorry for not being clear but, interior color is not my issue.

This line provides the incorrect or incomplete reference:

ThisWorkbook.Worksheets("GROSS").Range(p & i).Interior.Color = 255

It finds and fills Sheets(“GROSS”).[C4] (RED)

What I want is to fill cell C in “Robert’s” Row (which is currently [C31] on my test worksheet)
The incorrect/missing code needs to find “Robert’s” Row and fill cell C of that row.

dreid1011 · Aug 28, 2015

Big Lar said:

Thanks dreid1011, and sorry for not being clear but, interior color is not my issue.

This line provides the incorrect or incomplete reference:

ThisWorkbook.Worksheets("GROSS").Range(p & i).Interior.Color = 255

It finds and fills Sheets(“GROSS”).[C4] (RED)

What I want is to fill cell C in “Robert’s” Row (which is currently [C31] on my test worksheet)
The incorrect/missing code needs to find “Robert’s” Row and fill cell C of that row.

Hmm, the first thing I notice about that is Range uses a string. Is p a string? It looks like it's not as you are adding 1 to it's value above.

MARK858 · Aug 28, 2015

@Big Lar what is the significance of the line below

Code:

p = Sheets("PAYOUTS").Range("S4").Value + 1

as you immediately replace p with

Code:

p = "C"

and the same with

Code:

And Sheets(“Gross”).[ A31] = “Robert”

as you mention it but don't use A31 anywhere in the code

also with the below

Code:

If Range("T" & i).Value = cName Then

is it right that it always colors C4 as we know T4 = cName?

Big Lar · Aug 29, 2015

The code in my original post is my misguided attempt to solve this problem…And, as I stated…
it ALMOST works.

The idea is to indicate the “money hole” winners by red filling the appropriate cells on the “GROSS” scoreboard.

Sheets(“Payouts”).Range(S4:S7) are numeric values. These values are “money holes” in a golf event.
Sheets(“Payouts”).Range(T4:T7) are the names of the money hole winners.

Sheets(“GROSS”) is the “scoreboard” where:
Range(A3:A153) are all the event participant’s names
Range(B3:T153) are numeric values (their hole-by-hole golf scores)

So,
IF PAYOUTS [S4] value is 3 and “PAYOUTS” [T4] is Robert Jones
Find Robert Jones in “GROSS”[A3:A153] and fill red, the associated cell referenced in “PAYOUTS”[S4:S7] +1 (because hole-by-hole scores start in “GROSS” column B)

In other words:
If “PAYOUTS” S4 is 3 and T4 is Robert Jones
And, Robert Jones populates “GROSS”[A35], fill “GROSS”[D35] red

(This may create another issue…because the Values in “GROSS”[K3:K153] are the player’s total scores for the game’s front 9 holes.)
If “PAYOUTS” S5 is 11 and T5 is Jim Smith
And, Jim Smith populates “GROSS”[A16]. fill “GROSS”[M16] red

Maybe I should go lie down until I forget about how to make this work? LOL

MARK858 · Aug 29, 2015

Hi Big Lar, not sure I have read this 100% right but give the code below a whirl and we will take it from there.

Change value for MyConst for your starting number

Rich (BB code):

Sub Lars1()
    Dim MyConst As Long, x As Range, y As String, xy As Range
    MyConst = 1

    With Sheets("Payouts").Range("S4:S7")
        Set x = .Find(What:=MyConst, After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt _
                             :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    End With
    x.Offset(, 1).Interior.Color = RGB(255, 0, 0)

    y = x.Offset(, 1).Value

    With Sheets("Gross").Range("A3:A153")
        Set xy = .Find(What:=y, After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt _
                        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    End With
    
    If MyConst > 9 Then MyConst = MyConst + 1
    xy.Offset(, MyConst + 1).Interior.Color = RGB(255, 0, 0)
    
End Sub

Big Lar · Aug 29, 2015

Mark858,
Thanks for the continued interest. You effort is greatly appreciated.
 Interesting!

Here’s what exists on my Test Sheets:
“PAYOUTS”[S4] = 2 [T4] = Jones Robert
“PAYOUTS”[S5] = 9 [T5] = Smith Jim
“PAYOUTS”[S6] = 11 [T6] = Barnes Ben
“PAYOUTS”[S7] = 14 [T7] = McGee Tom

“GROSS”[A8] = Barnes Ben
“GROSS”[A25] = Smith Jim
“GROSS”[A31] = Jones Robert
“GROSS”[A37] = McGee Tom

When run, the Sub finds and fills “PAYOUTS”[T6] Barnes Ben
Then,
It finds and fills “GROSS”[C8]

So, it finds the correct row ([A8] is Ben’s row) which should then fill M8 (for hole #11)…but, C8 gets filled instead.

Debugging shows:
Set x = 11
Y = “”

Sadly, I lack the know how to go about correcting it...I just get error messages with every attempt. Yikes!

MARK858 · Aug 29, 2015

Sorry but how are you doing the debugging? X is a cell address not a number.

I'll probably post code when I get in to get more information.

Big Lar · Aug 29, 2015

Mark858,
I fixed it! I'm sure there's an extra elegant way to do it but, this variation of your code works on my test sheet.
I changed the fill color to green because there are already a number of Red filled cells on the sheet.
Woo!
Thanks for your assistance!

Code:

Sub Lars1()
    Dim MyConst As Long, x As Range, y As String, xy As Range
    MyConst = Sheets("Payouts").Range("S4")

    With Sheets("Payouts").Range("S4:S7")
        Set x = .Find(What:=MyConst, After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt _
                             :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    End With
    x.Offset(, 1).Interior.Color = vbGreen

    y = x.Offset(, 1).Value

    With Sheets("Gross").Range("A3:A153")
        Set xy = .Find(What:=y, After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt _
                        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    End With
    
    If MyConst > 9 Then MyConst = MyConst ' + 1
    xy.Offset(, MyConst).Interior.Color = vbGreen
    
 
    

    MyConst = Sheets("Payouts").Range("S5")

    With Sheets("Payouts").Range("S4:S7")
        Set x = .Find(What:=MyConst, After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt _
                             :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    End With
    x.Offset(, 1).Interior.Color = vbGreen

    y = x.Offset(, 1).Value

    With Sheets("Gross").Range("A3:A153")
        Set xy = .Find(What:=y, After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt _
                        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    End With
    
    If MyConst > 9 Then MyConst = MyConst ' + 1
    xy.Offset(, MyConst).Interior.Color = vbGreen
    
 
    
 
    MyConst = Sheets("Payouts").Range("S6")

    With Sheets("Payouts").Range("S4:S7")
        Set x = .Find(What:=MyConst, After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt _
                             :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    End With
    x.Offset(, 1).Interior.Color = vbGreen

    y = x.Offset(, 1).Value

    With Sheets("Gross").Range("A3:A153")
        Set xy = .Find(What:=y, After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt _
                        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    End With
    
    If MyConst > 9 Then MyConst = MyConst + 1
    xy.Offset(, MyConst).Interior.Color = vbGreen
    

 
    MyConst = Sheets("Payouts").Range("S7")

    With Sheets("Payouts").Range("S4:S7")
        Set x = .Find(What:=MyConst, After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt _
                             :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    End With
    x.Offset(, 1).Interior.Color = vbGreen

    y = x.Offset(, 1).Value

    With Sheets("Gross").Range("A3:A153")
        Set xy = .Find(What:=y, After:=.Cells(.Cells.Count), LookIn:=xlFormulas, LookAt _
                        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    End With
    
    If MyConst > 9 Then MyConst = MyConst + 1
    xy.Offset(, MyConst).Interior.Color = vbGreen
    
End Sub

Format the correct cell with vba

Big Lar

Well-known Member

Excel Facts

dreid1011

Well-known Member

Big Lar

Well-known Member

dreid1011

Well-known Member

MARK858

MrExcel MVP

Big Lar

Well-known Member

MARK858

MrExcel MVP

Big Lar

Well-known Member

MARK858

MrExcel MVP

Big Lar

Well-known Member

Similar threads

Forum statistics

Share this page

Format the correct cell with vba

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock