VBA changing background color of rows

HDfatboy03

Board Regular
Joined
May 23, 2010
Messages
62
I have a question about changing a rows color based on a few variables. I'm using a form "frmPost", it has a combo box "cboName" that populates cboName, txtColor, txtLoc & txtMark. Each employees name is associated with a color (Bob = Yellow) (Amber = Blue) (Jameson = Green). txtLoc identifies the row number (example...row 101). txtMark is an X that I would like to be placed in the appropriate column (Amber = Z) (Bob - AA) (Jameson AB)

So when Bob is selected I would like row 101 to be shaded in yellow from range A to Y and an x placed in column AA.

Below is what I have so far. Everything works ... just don't know how to start with the color change and right now I have the txtMark "x" hard coded to column AA

Private Sub cmdADD6_Click()
Dim iRow As Long
x = CLng(Me.txtLoc.Text)

'Dim x2 As String
'x2 = Me.cboName.Text

Dim ws As Worksheet
Dim ws2 As Worksheet

Set ws = Worksheets("Journal")
Set ws2 = Worksheets("HubertDb")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'copy the data to HubertDB for the marking and graphs
ws2.Cells(x, "AA").Value = Me.txtMark.Value

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtDate.Value
'ws.Cells(x, "A").Value = Me.txtDate.Value
ws.Cells(iRow, 2).Value = Me.cboName.Value
'ws.Cells(x, "B").Value = Me.cboName.Value
ws.Cells(iRow, 3).Value = Me.txtColor.Value
'ws.Cells(x, "C").Value = Me.txtColor.Value
ws.Cells(iRow, 4).Value = Me.txtMark.Value
'ws.Cells(x, "D").Value = Me.txtMark.Value
ws.Cells(iRow, 5).Value = Me.cboItem.Value
'ws.Cells(x, "E").Value = Me.cboItem.Value
ws.Cells(iRow, 6).Value = Me.txtVndr.Value
ws.Cells(iRow, 7).Value = Me.txtDesc.Value
'ws.Cells(x, "F").Value = Me.txtDesc.Value
ws.Cells(iRow, 8).Value = Me.txtCat.Value
'ws.Cells(x, "G").Value = Me.txtCat.Value
ws.Cells(iRow, 9).Value = Me.txtPage.Value
'ws.Cells(x, "H").Value = Me.txtPage.Value
ws.Cells(iRow, 10).Value = Me.txtWgt.Value
'ws.Cells(x, "I").Value = Me.txtWgt.Value
ws.Cells(iRow, 11).Value = Me.txtHgt.Value
'ws.Cells(x, "J").Value = Me.txtHgt.Value
ws.Cells(iRow, 12).Value = Me.txtWth.Value
'ws.Cells(x, "K").Value = Me.txtWth.Value
ws.Cells(iRow, 13).Value = Me.txtLth.Value
'ws.Cells(x, "L").Value = Me.txtLth.Value

'clear the data
'Me.txtDate.Value = ""
Me.txtDate.Value = ""
Me.txtLoc.Value = ""
Me.cboName.Value = ""
Me.txtColor.Value = ""
Me.txtMark.Value = ""
Me.cboItem.Value = ""
Me.txtVndr.Value = ""
Me.txtDesc.Value = ""
Me.txtCat.Value = ""
Me.txtPage.Value = ""
Me.txtWgt.Value = ""
Me.txtHgt.Value = ""
Me.txtWth.Value = ""
Me.txtLth.Value = ""


End Sub

thanks for any assistance

HDfatboy03
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

bertie

Well-known Member
Joined
Jun 12, 2009
Messages
1,869
I would use a Select Case statement for this:

Code:
   x = [COLOR=darkblue]CLng[/COLOR](txtLoc.Value)
   [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] cboName.Value
      [COLOR=darkblue]Case[/COLOR] "Bob"
         ws2.Range("A" & x & ":Y" & x).Interior.ColorIndex = 6 [COLOR=green]'yellow[/COLOR]
         ws2.Range("[COLOR=red]AA[/COLOR]" & x).Value = txtMark.Value
      [COLOR=darkblue]Case[/COLOR] "Amber"
         ws2.Range("A" & x & ":Y" & x).Interior.ColorIndex = 5 [COLOR=green]'blue[/COLOR]
         ws2.Range("[COLOR=red]Z[/COLOR]" & x).Value = txtMark.Value
      [COLOR=darkblue]Case[/COLOR] "Jameson"
         ws2.Range("A" & x & ":Y" & x).Interior.ColorIndex = 4 [COLOR=green]'green[/COLOR]
         ws2.Range("[COLOR=red]AB[/COLOR]" & x).Value = txtMark.Value
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
 

HDfatboy03

Board Regular
Joined
May 23, 2010
Messages
62
THANKS so much for the assistance ... I got it to work. Hope all is well where your at. How is the weather? We are trying to get warmer weather here in Ohio but winter still won't release its grip.

take care

HDfatboy03
 

bertie

Well-known Member
Joined
Jun 12, 2009
Messages
1,869
Good stuff.

We have had a long winter also. This week the temperature is creeping up towards the 20's. Time to turn the central heating off and get the t-shirt on.

Bertie
 

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,099
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top