It is necessary for some condition to fill in the row in the Excel table. I am trying to go through all the cells in the row and paint them separately. I tried the solutions found in Google:

Worksheets("List").Cells(1, 1).BackColor = RGB(255, 0, 0) Worksheets("List").Cells(1, 1).Interrior.Color = vbRed

In both cases, I get an error

Object doesn't support this property or method

Apparently, I do not correctly refer to the properties of the cell. How to paint the cells, and maybe it will turn out to paint the whole line or Range at once, and not by the cells?

  • In the first code, there is no such property. In the second - stupid typo (use Intellisence). - Akina

3 answers 3

You have an extra letter r attached :)

 .Interior.Color = vbRed .Interior.Color = RGB(255, 0, 0) .Interior.Color = 255

Work with leaf objects is slow. If there are a lot of cells and you need to fill it with a whole range, you can process in memory and fill the cells with one command.

  Sub PaintCells() Dim r1 As Range, r2 As Range Dim c As Range Set r1 = Range("A1:C10") ' Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½ Π² ΠΏΠ΅Ρ€Π΅ΠΌΠ΅Π½Π½ΡƒΡŽ r1.Interior.Pattern = xlNone ' ΡƒΠ±ΠΈΡ€Π°Π΅ΠΌ Π·Π°Π»ΠΈΠ²ΠΊΡƒ ' r1.Interior.ColorIndex = 0 ' ΠΈΠ»ΠΈ Ρ‚Π°ΠΊ For Each c In r1 ' Ρ†ΠΈΠΊΠ» ΠΏΠΎ ячСйкам Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½Π° If c.Value = 25 Then ' Ссли условиС выполняСтся If r2 Is Nothing Then ' Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½ Π΅Ρ‰Π΅ пустой Set r2 = c ' Ρ„ΠΎΡ€ΠΌΠΈΡ€ΡƒΠ΅ΠΌ Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½ Else Set r2 = Union(r2, c) ' пополняСм Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½ End If End If Next c If Not r2 Is Nothing Then r2.Interior.Color = 255 ' Π·Π°Π»ΠΈΠ²Π°Π΅ΠΌ Set r1 = Nothing: Set r2 = Nothing ' освобоТдаСм ΠΏΠ°ΠΌΡΡ‚ΡŒ End Sub

Fill lines by condition in the cell:

 For i = 1 To r1.Rows.Count ' Ρ†ΠΈΠΊΠ» ΠΏΠΎ строкам Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½Π° If r1(i, 1).Value = 25 Then ' Ссли лСвая ячСйка Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½Π°... If r2 Is Nothing Then ' Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½ Π΅Ρ‰Π΅ пустой Set r2 = Range(Cells(i, 1), Cells(i, 3)) ' Ρ„ΠΎΡ€ΠΌΠΈΡ€ΡƒΠ΅ΠΌ Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½ Else Set r2 = Union(r2, Range(Cells(i, 1), Cells(i, 3))) ' пополняСм Π΄ΠΈΠ°ΠΏΠ°Π·ΠΎΠ½ End If End If Next i

    Use simply:

     ActiveWorkbook.Worksheets("Лист1").Range(Cells(1, 1), Cells(1, 10)).Interior.Color = 100

    for example, to fill the 1st row from 1 to 10 cell.

    PS You have an error in the word Interrior , there is 1 letter r , therefore an error about an unfamiliar method pops up.

      Is it possible to paint the entire line or Range at once, and not by cells?

      Well, you need to paint the string, not the cells:

       worksheets("Sheet1").Rows(1).Interior.Color=vbRed
      • So I will fill only non-empty cells or, in general, all the cells in a row up to the maximum possible length of the string? - Viktorov
      • Well, I did not ask for the line to be painted ... and if you need to paint the Range - just specify it as an object as an object. all at once, and not one cell from its range. Type worksheets ("Sheet1"). Range ("A1: B2, C4: D5"). Interior.Color = vbRed . - Akina