lundi 22 mai 2017

Change Highlight Color After User input in Excel vba

I have a piece of code that will allow me to find the next free cell in column F, where an operator will input a Weight.

I wanted to help by highlighting the cells in a range close to where the input needs to be done (this can help to check that the entry is correct without filtering).

I can do that with the code below but I'm trying to remove the highlighting after the cell is written and I'm failing. I tried an approach with 'Do Until' but was also not satisfactory. The code runs but it does not remove the highlight once the user adds a value.

I have also tried using Wait functions but they freeze Excel completely (I cannot modify any value). Additionally, when I run in debug and use a random iteration to modify the Cell value, my code works.

    'Find the last non-blank cell in column F (aka, column 6)
    ' We will add i rows to make the ith blank (in the for loop)
            PreFree = Cells(Rows.Count, 6).End(xlUp).Row

            NextFree = PreFree + 1

    ' Select Cell for manual input
          Range("F" & NextFree).Select


          'Do Until emptyWeight = False
          If ThisWorkbook.Sheets("Input").Range("F" & NextFree) = "" Then
           emptyWeight = True
             Range(Cells(NextFree, "C"), Cells(NextFree, "F")).Interior.Color = RGB(181, 244, 0)
           Else
             Range(Cells(NextFree, "C"), Cells(NextFree, "F")).Interior.Color = RGB(255, 255, 255)
            emptyWeight = False
           End If

Aucun commentaire:

Enregistrer un commentaire