Filling an Excel cell with colour

[darinmc]

Hi
I have the below code which checks data and where necessary, creates a new line.
This was created for me, so I dont know what to use to fill cells with colours?

Sub AdjustData()
Dim LastUsedRow As Long
Dim i As Long
Dim j As Long

   Application.ScreenUpdating = False
   With Worksheets("Sheet1")
     LastUsedRow = .Cells(65536, 1).End(xlUp).Row
     If LastUsedRow > 1 Then
       i = 2
       Do
         If .Cells(i, 2).Value = 1 And .Cells(i, 3) > 37.5 Then
           j = i + 1
           .Cells(j, 1).EntireRow.Insert
           .Cells(j, 1).Value = .Cells(i, 1).Value
           .Cells(j, 2).Value = 2
--------------------------------------------
[b][COLOR=red] I would like to fill .Cells(j, 2) and .Cells(j, 3) with yellow[/color][/b]
------------------------------------
           .Cells(j, 3).Value = .Cells(i, 3).Value - 37.5
           .Cells(i, 3) = 37.5
           .Cells(j, 4).Value = .Cells(i, 4).Value
           i = j
           LastUsedRow = LastUsedRow + 1
         End If
         i = i + 1
       Loop Until i > LastUsedRow
     End If
   End With
   Application.ScreenUpdating = True
End Sub

Hope u can help
Thx
Darin

 

[AnotherHiggins]

FYI:

Macro Recorder is your friend!!

-Tools > Macro > Record New Macro
-Select any ol' name
-color a cell yellow
-Press the STOP button

Observe the new code you just created!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[darinmc]

Thx
I haven't really done much programming, basically none in excel.

I recorded a macro with these results... messed around a little

    Range("B9").Select
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With

Sub AdjustData()
Dim LastUsedRow As Long
Dim i As Long
Dim j As Long

   Application.ScreenUpdating = False
   With Worksheets("Sheet1")
     LastUsedRow = .Cells(65536, 1).End(xlUp).Row
     If LastUsedRow > 1 Then
       i = 2
       Do
         If .Cells(i, 2).Value = 1 And .Cells(i, 3) > 37.5 Then
           j = i + 1
           .Cells(j, 1).EntireRow.Insert
           .Cells(j, 1).Value = .Cells(i, 1).Value
           .Cells(j, 2).Value = 2
[b][COLOR=red]IS THERE A WAY TO GROUP THE BELOW OR DO I HAVE TO DO IT INDIVIDUALLY?? i GOT THE 2 TO WORK BUT THEY BOTH LOOK A BIT CLUMSY!!!
           .Cells(i, 1).Interior.ColorIndex = 19
           .Cells(i, 2).Interior.ColorIndex = 19
           .Cells(i, 3).Interior.ColorIndex = 19
           .Cells(i, 4).Interior.ColorIndex = 19[/color][/b]
    [b][COLOR=blue]With .Cells(j, 2).Interior
        .ColorIndex = 19
        .Pattern = xlSolid
    End With
    With .Cells(j, 3).Interior
        .ColorIndex = 19
        .Pattern = xlSolid
    End With[/color][/b]
'4=green 3=red 5=blue 6=yellow 7=pink 8=turq 15=LightGrey 19=Lightyell
           .Cells(j, 3).Value = .Cells(i, 3).Value - 37.5
           .Cells(i, 3) = 37.5
           .Cells(j, 4).Value = .Cells(i, 4).Value
           i = j
           LastUsedRow = LastUsedRow + 1
         End If
         i = i + 1
       Loop Until i > LastUsedRow
     End If
   End With
   Application.ScreenUpdating = True
End Sub

Thx

Darin

 

[SkipVought]

    .range(.Cells(i, 1), .Cells(i, 4)).Interior.ColorIndex = 19

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}