VBA changing background color of rows

[HDfatboy03]

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

 

[bertie]

I would use a Select Case statement for this:

   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]

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]

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