Search and Color

Basic Exercise

Statement

A spices shop made a discount campaign to promote the opening of a new store. To that purpose some products were sold for US$ 1,00 (one dollar) to attract more clients. In this exercise, you need to locate the items sold for one dollar in the worksheet. Once the items are found color to green the background of both item name and selling price cells for each of those items Additionally you need to write "Sold Out" by the side of those items (in column "C").

Suggested Concepts:

• Dim - Declares a variable.
• For Next - Repeats a group of statements a specified number of times.
• If Else - Conditionally executes a group of statements, depending on the value of an expression.
• Offset - Shifts the position of the range.
• Resize - Resize the selection.

Remember that in most cases there is more than one way to solve the exercise.

Hint: Condition the loop to find the desired value and subsequently adjust the range.

    Sub Solution()

    Dim LastRow As Long
    Dim CellVal As Long
    Dim i As Integer

    LastRow = Cells(Rows.Count, 2).End(xlUp).Row

    For i = 3 To LastRow
        CellVal = Cells(i, 2).Value
        If CellVal = 1 Then
        Cells(i, 2).Offset(, 1).Value = "Sold Out"
        Cells(i, 1).Resize(, 2).Interior.Color = RGB(140, 198, 63)
        End If
    Next i

    End Sub

• Previous exercise
• Next exercise

• report error/feedback

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

© 2024 SuperExcelVBA | ABOUT