1

So after creating a table, I have a column of Yes, No and N/A that needs to be green for Yes, Red for No and gray for N/A. Not the entire row just the column.

I'm not a beginner, but I'm not an expert at coding...here is my code:

With .Shapes.AddTable(5, 5, 0, 140, 720, 40)                                       
' NumRows / NumColumns / Left / Top / Width / Height
  .Table.ApplyStyle "{5940675A-B579-460E-94D1-54222C63F5DA}"                  
  ' Table Style for No Style, Table Grid
  .Table.Cell(1, 1).Shape.TextFrame.TextRange.Text = "Text1"
  .Table.Cell(1, 2).Shape.TextFrame.TextRange.Text = "TeXT2"
  .Table.Cell(1, 3).Shape.TextFrame.TextRange.Text = "Text3"
  .Table.Cell(1, 4).Shape.TextFrame.TextRange.Text = "Text4"
  .Table.Cell(1, 5).Shape.TextFrame.TextRange.Text = "Text5"
  R = 1
  With .Table
    For C = 1 To 5
      .Cell(R, C).Shape.TextFrame.TextRange.Font.Size = 8
      .Cell(R, C).Shape.TextFrame.TextRange.Font.Color.RGB = RGB(255, 255, 255)
      .Cell(R, C).Shape.TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignCenter
    Next
    R = R + 1
  End With

  .Table.Columns(1).Width = 140
  .Table.Columns(2).Width = 60
  .Table.Columns(3).Width = 60
  .Table.Columns(4).Width = 100
  .Table.Columns(5).Width = 360
  .Fill.BackColor.RGB = RGB(255, 255, 255)
  For Each cl In .Table.Rows(1).Cells
    cl.Shape.Fill.BackColor.RGB = RGB(0, 32, 96)
    cl.Shape.Fill.ForeColor.RGB = RGB(0, 32, 96)
    cl.Shape.TextFrame.TextRange.Characters.Font.Bold = True
    cl.Shape.TextFrame.VerticalAnchor = msoAnchorBottom
  Next cl

  ' Adding data to table ------------------------
  R = 2
  With .Table
    While Not rs26.EOF
      For C = 1 To 5
        .Cell(R, C).Shape.TextFrame.TextRange.Text = Nz(rs26.Fields(C - 1)) 
        .Cell(R, C).Shape.TextFrame.TextRange.Font.Size = 8
        .Cell(R, C).Shape.TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignLeft
      Next    'c column
      rs26.MoveNext
      R = R + 1
    Wend
    rs26.Close
  End With

  ' Having trouble getting this part of the code to work ------------------ 
  With .Table
    If .Cell(R, 2).Shape.TextFrame.TextRange.Text = "No" Then
      .Cell(R, 2).Shape.Fill.BackColor.RGB = RGB(255, 0, 0) ' Red
      .Cell(R, 2).Shape.Fill.ForeColor.RGB = RGB(255, 0, 0)
    ElseIf .Cell(R, 2).Shape.TextFrame.TextRange.Text = "Yes" Then
      .Cell(R, 2).Shape.Fill.BackColor.RGB = RGB(146, 208, 80)  ' Green
      .Cell(R, 2).Shape.Fill.ForeColor.RGB = RGB(146, 208, 80)
    Else
      .Cell(R, 2).Shape.Fill.BackColor.RGB = RGB(166, 166, 166) ' Gray
      .Cell(R, 2).Shape.Fill.ForeColor.RGB = RGB(166, 166, 166)
    End If
  End With

End With ' table for chart

I put a comment line above the code I'm having an issue with. When it gets to this part of the code it stops running.

I've tried putting this part of the code in several different places, but still won't work.

Hope I've given enough information for someone to help.

Thanks for your time.

Rdster
  • 1,846
  • 1
  • 16
  • 30
drcoz
  • 11
  • 4

1 Answers1

0

Well, since I posted this question, I continued to play around with the code and came up with the answer. I knew an IF...THEN would work, I just had to put in the right combination and once I did, I got the code to work...see solution below:

    R = 2
    With .Table
      While Not rs26.EOF
        For C = 1 To 5
         .Cell(R, C).Shape.TextFrame.TextRange.Text = Nz(rs26.Fields(C - 1))
         .Cell(R, C).Shape.TextFrame.TextRange.Font.Size = 8
         .Cell(R, C).Shape.TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignLeft
           If Nz(rs26.Fields(2 - 1)) = "Yes" Then
            .Cell(R, 2).Shape.Fill.ForeColor.RGB = RGB(146,208,80)    'Green
            .Cell(R, 2).Shape.Fill.BackColor.RGB = RGB(146, 208, 80)
            .Cell(R, 2).Shape.TextFrame.TextRange.Font.Color.RGB = RGB(255, 255, 255)
            .Cell(R, 2).Shape.TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignCenter
           ElseIf Nz(rs26.Fields(2 - 1)) = "No" Then
            .Cell(R, 2).Shape.Fill.ForeColor.RGB = RGB(255, 0, 0)   'Red
            .Cell(R, 2).Shape.Fill.BackColor.RGB = RGB(255, 0, 0)
            .Cell(R, 2).Shape.TextFrame.TextRange.Font.Color.RGB = RGB(255, 255, 255)
            .Cell(R, 2).Shape.TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignCenter
           Else
            .Cell(R, 2).Shape.Fill.BackColor.RGB = RGB(166, 166, 166) ' Gray
            .Cell(R, 2).Shape.Fill.ForeColor.RGB = RGB(166, 166, 166)
            .Cell(R, 2).Shape.TextFrame.TextRange.ParagraphFormat.Alignment = ppAlignCenter
           End If
        Next    'c column
        rs26.MoveNext
        R = R + 1
      Wend
      rs26.Close
    End With

I basically embedded the IF...THEN statement inside the FOR statement while the data is being loaded into the table. Before I was trying to do this after the data was already in the table.

drcoz
  • 11
  • 4