cell colouring EXCEL

crmpicco · April 19th, 2005, 08:26 AM

I have this code:

Code:

If stripWhiteSpace(Left(Trim(UCase(Range(sRange).Text)), Len(Trim(UCase(gaDepSearch(j)))))) <> stripWhiteSpace(Trim(UCase(gaDepSearch(j)))) Then
                    Call WriteColourLog(Application.ActiveSheet.Name, "DEPARTURE STRING: " & _
                                                                      Chr(34) & UCase(gaDepSearch(j)) & Chr(34), sRange)
                Else
                    gbFoundDep = True
                    Range(sRange).Interior.ColorIndex = iColour

                    tempCol = Asc(extractString(sRange))
                    tempRow = extractNumber(sRange)

                     For k = tempCol To iTotalRows
                        sRange = Chr(tempCol) & k
                        tempRange = Chr(tempCol + 1) & k
                        If hasCityCode(Range(sRange).Text) Then

                                Range(sRange).Interior.ColorIndex = iColour

                        End If
                    Next
                End If

I want to color that cell and all cells to the right, until the are empty.

www.crmpicco.co.uk
www.crmpicco.co.uk.tt
www.milklemonadechocolate.uk.tt
www.griswolds.uk.tt

mega · April 19th, 2005, 09:43 AM

ok??

- mega
Moving to C# .NET

mjppaba · April 20th, 2005, 11:14 AM

Try surrounding the code with a do until...loop.

Range("A1").Select
Do Until ActiveCell.Value = ""
    Do Until ActiveCell.Value = ""
        If stripWhiteSpace(Left(Trim(UCase(Range(sRange).Text )), Len(Trim(UCase(gaDepSearch(j)))))) <> stripWhiteSpace(Trim(UCase(gaDepSearch(j)))) Then
            Call WriteColourLog(Application.ActiveSheet.Name, "DEPARTURE STRING: " & _
            Chr(34) & UCase(gaDepSearch(j)) & Chr(34), sRange)
        Else
            gbFoundDep = True
            Range(sRange).Interior.ColorIndex = iColour

            tempCol = Asc(extractString(sRange))
            tempRow = extractNumber(sRange)

            For k = tempCol To iTotalRows
                sRange = Chr(tempCol) & k
                tempRange = Chr(tempCol + 1) & k
                If hasCityCode(Range(sRange).Text) Then
                    Range(sRange).Interior.ColorIndex = iColour
                End If
            Next
        End If
    ActiveCell.Offset(0, 1).Select
    Loop
ActiveCell.Offset(1, -ActiveCell.Column + 1).Select
Loop

cheers

Matt