Trasparent color excel vba 2007 not working

**valgo54** · 07-26-2013, 03:34 PM

Hello
I have this problem with this macro.
The first 5 rows contains description, the user insert data starting from row number 6
Rows 1 , 3 and 5 , they contain user information , are colored in yellow.
Why during insert data row 6 is trasparent color , row 7 is colored in yellow, row 8 trasparent color, row 9 in yellow and so on ( one row trasparent, one row yellow). I want to have for all data inserted into the rows trasparent color. in attachemnt the excel file with macro to renaim in .xlms. My excel version is 2007.
Thanks

Here is is the code

Sub Pulizia_righe()
Dim max_rows, rows_true, No_Of_Files, r, r1, Num_righe_testata As Integer


Dim pos_punto As Byte

max_rows = ActiveSheet.UsedRange.Rows.Count

'File_Path = Worksheets("Foglio1").Cells(2, 1)
r = 6



Do While r <= max_rows
    If Sheets("Foglio1").Cells(r, 1).Value = Empty Then
        Exit Do
    End If

    Sheets("Foglio1").Cells(r, 1).Value = Empty
    r = r + 1
Loop
Range("A6").Select
MsgBox "clean completed.", vbInformation
End Sub

**Leith Ross** · 07-26-2013, 08:02 PM

Hello valgo54,

This change to your macro will clear the cells in column "A". However, it will not change the cell color. Rows 1 through 5 will remain the same.

Sub Pulizia_righe()

  ' Questa pulisce i campi correttamente senza l'effetto una riga grigia e una no ( 26/07/2013)
    Dim max_rows, rows_true, No_Of_Files, r, r1, Num_righe_testata As Integer
    Dim File_Path, fin_nome_file, iniz_nome_file, GivenLOcation As String
    Dim pos_punto As Byte
    
    Dim Rng As Range
    Dim RngEnd As Range

      ' File_Path = Worksheets("Foglio1").Cells(2, 1)
        r = 6

        Set Rng = Sheets("Foglio1").Cells(r, "A")
        Set RngEnd = Sheets("Foglio1").Cells(Rows.Count, "A").End(xlUp)
        Set Rng = IIf(RngEnd.Row > Rng.Row, Sheets("Foglio1").Range(Rng, RngEnd), Rng)
    
        Rng.ClearContents
        Rng.Cells(1, 1).Select
        
        MsgBox "Pulizia completata.", vbInformation
        
End Sub

**valgo54** · 07-28-2013, 01:10 PM

Hello Leith for your answer
Supposig to have rows 1, 3 and 5 with yellow color. After I ran your macro this clean corretly all the rows with values starting from row 6. Now if I enter again values starting from row 6 I have row 6 with trasparent color, row 7 in yellow, row 8 traparent color , row 9 yellow color and so on.
Do you have any idea for that
Thanks