Conditional Format in VBA Word

[Singing Villan]

Have a table in Word (eg 20x20), and I wish to look at cells (6,1)-(6,6) &
apply conditional formatting. Code works for all cells, but I can't work out
how to address specific cells.

Dim RAG1, RAG2 As String
Dim iRow, jCol As Integer

For Each t In ActiveDocument.Tables
For Each Cell In t.Range.Cells

For iRow = 6 To 6
For jCol = 1 To 6

RAG1 = Cells(iRow, jCol).Selection.Text ' Compile error *******
RAG2 = Left(RAG1, 1)
Select Case RAG2
Case "A"
Cell.Shading.BackgroundPatternColor = wdColorGold
Case "R"
Cell.Shading.BackgroundPatternColor = wdColorRed
Case "G"
Cell.Shading.BackgroundPatternColor = wdColorBrightGreen
Case "C"
Cell.Shading.BackgroundPatternColor = wdColorBlue
Case "H"
Cell.Shading.BackgroundPatternColor = wdColorYellow
Case Else
Cell.Shading.BackgroundPatternColor = wdColorLightYellow
End Select

Next
Next
Next
Next

 

[macropod]

Hi Singing Villan,

Try:
Sub Test()
Dim strTxt As String, oTbl As Table
Dim iRow, jCol As Integer
iRow = 6
For Each oTbl In ActiveDocument.Tables
For jCol = 1 To 6
With oTbl.Cell(iRow, jCol)
strTxt = Left(.Range.Text, 1)
Select Case strTxt
Case "A"
.Shading.BackgroundPatternColor = wdColorGold
Case "R"
.Shading.BackgroundPatternColor = wdColorRed
Case "G"
.Shading.BackgroundPatternColor = wdColorBrightGreen
Case "C"
.Shading.BackgroundPatternColor = wdColorBlue
Case "H"
.Shading.BackgroundPatternColor = wdColorYellow
Case Else
.Shading.BackgroundPatternColor = wdColorLightYellow
End Select
End With
Next
Next
End Sub

 

[Graham Mayor]

The following will set the background colour of the first six cells in the
sixth row of all tables in the document according to the first letter of the
content of those cells.

Dim Rag1 As Range
Dim t, iRow, jCol As Long
iRow = 6
For t = 1 To ActiveDocument.Tables.Count
With ActiveDocument.Tables(t)
For jCol = 1 To 6
Set Rag1 = .Cell(iRow, jCol).Range
Select Case UCase(Rag1.Characters(1))
Case "A"
Rag1.Shading.BackgroundPatternColor = wdColorGold
Case "R"
Rag1.Shading.BackgroundPatternColor = wdColorRed
Case "G"
Rag1.Shading.BackgroundPatternColor = wdColorBrightGreen
Case "C"
Rag1.Shading.BackgroundPatternColor = wdColorBlue
Case "H"
Rag1.Shading.BackgroundPatternColor = wdColorYellow
Case Else
Rag1.Shading.BackgroundPatternColor = wdColorLightYellow
End Select
Next jCol
End With
Next t


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Pesach Shelnitz]

You are getting the compile error because the line where you tried to copy
the text in a cell into a String does not specify the object that Cells
belongs to. Also, I don't think that using the Cells property of the Range
object is the most efficient way to achieve what you are trying to do. The
Cells property holds a collection of all the cells in the range, in which the
cells are numbered from 1 to the total number of cells in the range, rather
than by row number and column number. In my opinion, it would be more
efficient to use the Cell property of the Table object instead. If you use
this property, there is no need for the For loop that iterates through all
the cells in the table. There is also no need for a For loop that iterates
through only one row in the table. You can just set iRow equal to 6.

Since a document may contain a table with less than 6 rows or less than 6
columns, to avoid the errors that such tables would generate, I would add a
couple of If statements to prevent the macro from looking for cells that
don't exist.

After making these changes to your macro, I have the following:

Sub ShadeCertainCells()

Dim t As table
Dim c As Cell
Dim numCols As Integer
Dim iRow As Integer
Dim jCol As Integer
Dim RAG1 As String
Dim RAG2 As String

For Each t In ActiveDocument.Tables
If t.Rows.Count >= 6 Then
iRow = 6
If t.Columns.Count < 6 Then
numCols = t.Columns.Count
Else
numCols = 6
End If
For jCol = 1 To numCols
Set c = t.Cell(iRow, jCol)
RAG1 = c.Range.Text
RAG2 = Left(RAG1, 1)
Select Case RAG2
Case "A"
c.Shading.BackgroundPatternColor = wdColorGold
Case "R"
c.Shading.BackgroundPatternColor = wdColorRed
Case "G"
c.Shading.BackgroundPatternColor = wdColorBrightGreen
Case "C"
c.Shading.BackgroundPatternColor = wdColorBlue
Case "H"
c.Shading.BackgroundPatternColor = wdColorYellow
Case Else
c.Shading.BackgroundPatternColor = wdColorLightYellow
End Select
Next
End If
Next
End Sub

 

[Singing Villan]

Perfect, many thanks...

The following will set the background colour of the first six cells in the
sixth row of all tables in the document according to the first letter of the
content of those cells.

Dim Rag1 As Range
Dim t, iRow, jCol As Long
iRow = 6
For t = 1 To ActiveDocument.Tables.Count
With ActiveDocument.Tables(t)
For jCol = 1 To 6
Set Rag1 = .Cell(iRow, jCol).Range
Select Case UCase(Rag1.Characters(1))
Case "A"
Rag1.Shading.BackgroundPatternColor = wdColorGold
Case "R"
Rag1.Shading.BackgroundPatternColor = wdColorRed
Case "G"
Rag1.Shading.BackgroundPatternColor = wdColorBrightGreen
Case "C"
Rag1.Shading.BackgroundPatternColor = wdColorBlue
Case "H"
Rag1.Shading.BackgroundPatternColor = wdColorYellow
Case Else
Rag1.Shading.BackgroundPatternColor = wdColorLightYellow
End Select
Next jCol
End With
Next t


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>