How To Change Excel Colour

[DMT Dave]

Hi Guy's, has anyone come across this before, there is always a gap and the excel colour and message of OK or Running Low or Very Low in the cells has a gap and moves to Row 10 when all is set to go as far as row 9 ??



I don't want to send full code as it's large but I can't figure out why when there is nothing else programmed to go into these Excel cells

The last output to excel, row 8 is blank

If Qty101 >= "5" Then.Worksheets(1).Cells(3, 15).Interior.ColorIndex = 4
.Worksheets(1).Cells(3, 15) = "OK"
.Worksheets(1).Cells(3, 15).Font.Bold = False
End If
If Qty101 >= "2" Then
If Qty101 <= "4" Then
.Worksheets(1).Cells(3, 15).Interior.ColorIndex = 6
.Worksheets(1).Cells(3, 15) = "RUNNING LOW"
.Worksheets(1).Cells(3, 15).Font.Bold = False
End If
End If
If Qty101 <= "1" Then
.Worksheets(1).Cells(3, 15).Interior.ColorIndex = 3
.Worksheets(1).Cells(3, 15) = "VERY LOW"
.Worksheets(1).Cells(3, 15).Font.Bold = True
End If
If Qty102 >= "5" Then
.Worksheets(1).Cells(4, 15).Interior.ColorIndex = 4
.Worksheets(1).Cells(4, 15) = "OK"
.Worksheets(1).Cells(4, 15).Font.Bold = False
End If
If Qty102 >= "2" Then
If Qty102 <= "4" Then
.Worksheets(1).Cells(4, 15).Interior.ColorIndex = 6
.Worksheets(1).Cells(4, 15) = "RUNNING LOW"
.Worksheets(1).Cells(4, 15).Font.Bold = False
End If
End If
If Qty102 <= "1" Then
.Worksheets(1).Cells(4, 15).Interior.ColorIndex = 3
.Worksheets(1).Cells(4, 15) = "VERY LOW"
.Worksheets(1).Cells(4, 15).Font.Bold = True
End If
If Qty103 >= "5" Then
.Worksheets(1).Cells(5, 15).Interior.ColorIndex = 4
.Worksheets(1).Cells(5, 15) = "OK"
.Worksheets(1).Cells(5, 15).Font.Bold = False
End If
If Qty103 >= "2" Then
If Qty103 <= "4" Then
.Worksheets(1).Cells(5, 15).Interior.ColorIndex = 6
.Worksheets(1).Cells(5, 15) = "RUNNING LOW"
.Worksheets(1).Cells(5, 15).Font.Bold = False
End If
End If
If Qty103 <= "1" Then
.Worksheets(1).Cells(5, 15).Interior.ColorIndex = 3
.Worksheets(1).Cells(5, 15) = "VERY LOW"
.Worksheets(1).Cells(5, 15).Font.Bold = True
End If
If Qty104 >= "5" Then
.Worksheets(1).Cells(6, 15).Interior.ColorIndex = 4
.Worksheets(1).Cells(6, 15) = "OK"
.Worksheets(1).Cells(6, 15).Font.Bold = False
End If
If Qty104 >= "2" Then
If Qty104 <= "4" Then
.Worksheets(1).Cells(6, 15).Interior.ColorIndex = 6
.Worksheets(1).Cells(6, 15) = "RUNNING LOW"
.Worksheets(1).Cells(6, 15).Font.Bold = False
End If
End If
If Qty104 <= "1" Then
.Worksheets(1).Cells(6, 15).Interior.ColorIndex = 3
.Worksheets(1).Cells(6, 15) = "VERY LOW"
.Worksheets(1).Cells(6, 15).Font.Bold = True
End If
If Qty105 >= "5" Then
.Worksheets(1).Cells(7, 15).Interior.ColorIndex = 4
.Worksheets(1).Cells(7, 15) = "OK"
.Worksheets(1).Cells(7, 15).Font.Bold = False
End If
If Qty105 >= "2" Then
If Qty105 <= "4" Then
.Worksheets(1).Cells(7, 15).Interior.ColorIndex = 6
.Worksheets(1).Cells(7, 15) = "RUNNING LOW"
.Worksheets(1).Cells(7, 15).Font.Bold = False
End If
End If
If Qty105 <= "1" Then
.Worksheets(1).Cells(7, 15).Interior.ColorIndex = 3
.Worksheets(1).Cells(7, 15) = "VERY LOW"
.Worksheets(1).Cells(7, 15).Font.Bold = True
End If
If Qty106 >= "5" Then
.Worksheets(1).Cells(8, 15).Interior.ColorIndex = 4
.Worksheets(1).Cells(8, 15) = "OK"
.Worksheets(1).Cells(8, 15).Font.Bold = False
End If
If Qty106 >= "2" Then
If Qty106 <= "4" Then
.Worksheets(1).Cells(8, 15).Interior.ColorIndex = 6
.Worksheets(1).Cells(8, 15) = "RUNNING LOW"
.Worksheets(1).Cells(8, 15).Font.Bold = False
End If
End If
If Qty106 <= "1" Then
.Worksheets(1).Cells(8, 15).Interior.ColorIndex = 3
.Worksheets(1).Cells(8, 15) = "VERY LOW"
.Worksheets(1).Cells(8, 15).Font.Bold = True
End If
If Qty107 >= "5" Then
.Worksheets(1).Cells(9, 15).Interior.ColorIndex = 4 (goes onto row 10)
.Worksheets(1).Cells(9, 15) = "OK" (goes onto row 10)
.Worksheets(1).Cells(9, 15).Font.Bold = False (goes onto row 10)
End If
If Qty107 >= "2" Then
If Qty107 <= "4" Then
.Worksheets(1).Cells(9, 15).Interior.ColorIndex = 6 (goes onto row 10)
.Worksheets(1).Cells(9, 15) = "RUNNING LOW" (goes onto row 10)
.Worksheets(1).Cells(9, 15).Font.Bold = False (goes onto row 10)
End If
End If
If Qty107 <= "1" Then
.Worksheets(1).Cells(9, 15).Interior.ColorIndex = 3
.Worksheets(1).Cells(9, 15) = "VERY LOW"
.Worksheets(1).Cells(9, 15).Font.Bold = True
End If

[ssanfu]

Not much to go on here. Looking at the code you posted, there is nothing obviously wrong that I can see.

It is not clear if the code is executed in Access or in Excel.

Could/would you post the Access/Excel?


I re-wrote your code and tested as far as I could.

    Dim qty As String
    Dim x As Integer
    Dim theRow As Integer

    theRow = 2   'beginning Excel row

    For x = 1 To 7
        qty = Me("qty" & x + 100)
        theRow = theRow + 1
        
        Select Case qty
            Case Is >= 5
                '                .Worksheets(1).Cells(theRow, 15).Interior.ColorIndex = 4
                '                .Worksheets(1).Cells(theRow, 15) = "OK"
                '                .Worksheets(1).Cells(theRow, 15).Font.Bold = False
                Debug.Print qty, theRow
            Case 2 To 4
                '                .Worksheets(1).Cells(theRow, 15).Interior.ColorIndex = 6
                '                .Worksheets(1).Cells(theRow, 15) = "RUNNING LOW"
                '                .Worksheets(1).Cells(theRow, 15).Font.Bold = False
                Debug.Print qty, theRow
            Case Is <= 1
                '                .Worksheets(1).Cells(theRow, 15).Interior.ColorIndex = 3
                '                .Worksheets(1).Cells(theRow, 15) = "VERY LOW"
                '                .Worksheets(1).Cells(theRow, 15).Font.Bold = True
                Debug.Print qty, theRow
        End Select
    Next

I am guessing that "Qty101" through "Qty107" are controls on a form...???

[accesstos]

I suspect that the original poster just tries to override the Conditional Format of Excel and refers to some cells via variables like "Qty001" etc.

[DMT Dave]

Thank you for your reply, yes it is written from Access to Excel, 101 to 107 is a table sort number so the product is in order on Access screen (Form datasheet)

Just to note as above, 101 to 107 is not the volume in stock, it is a sort number and im trying to out put a quantity based on the sort number to get it onto the correct cells, here is a snippet

They are all written set as Long as the field is a number field

You will see in the capture that row 10 should be row 9 but sort 101 to sort 105 are correct

[accesstos]

Dave, I think that you make your life more difficult than it needs!
In the attachment there are two ways (an Excel worksheet and an Access report) without any line of code.

Download the CF.zip file to your machine,
extract the CF folder,
open the CF.mdb file,
open the CF.xlsx file,
update the connection to the CF.mdb file,
open the rptCF report in Access and see the results,
make some changes to the quantities in ItemQty of tblItems,
return to the report to see the results (refresh all),
return to the CF.xlsx file,
refresh the connection (Ctrl+Alt+F5) and see the results.

Pay attention to the formulas and the conditional formatting rules of the column with the messages of the report and of the worksheet.

Good luck,
John