Enact two different changes to cells (reduce value by 50% AND highlight red) in one sub

Question

The code below is used to halve the value in cell A (where the corresponding cell in column B is above 3). Is there any way to also highlight the cell in red (based on the same condition). So, if the corresponding cell in column B is above 3, it is halved AND highlighted Red:

Sub halveandcolorchange()
    Dim cell As Range, rng As Range, A As Range, LastRow As Long

    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    Set rng = Range("B1:B" & LastRow)
    
    For Each cell In rng
        Set A = cell.Offset(0, -1)
        If cell.Value > 3 Then A.Value = A.Value / 2
    Next cell
End Sub

Use the macro recorder and then do the highlight you want, after you are done, look at the code for the syntax and value you did to the cell and apply it to `A` this time. Since you are doing 2 lines of code under the `Then` branch, move `A.Value = A.Value / 2` to next line, add the line that highlights the cell then end it with `End If` above `Next cell`. — Raymond Wu, Aug 12 '21 at 13:26
After using the macro recorder make sure to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to get rid of all the select statements. — Pᴇʜ, Aug 12 '21 at 13:28
Syntax error when I do the following: Dim cell As Range, rng As Range, A As Range, LastRow As Long LastRow = Cells(Rows.Count, "B").End(xlUp).Row Set rng = Range("B1:B" & LastRow) For Each cell In rng Set A = cell.Offset(0, -1) If cell.Value > 3 Then A.Value = A.Value / 2 cell.Value > 3 Then cell.Interior.ColorIndex = 3 End If Next cell End Sub — Lysander2001, Aug 12 '21 at 14:41
Please [edit] your original question to add code. In comments code gets useless as it is not formatted. — Pᴇʜ, Aug 12 '21 at 14:43

Raymond Wu · Answer 1 · 2021-08-12T15:41:17.140

0

Please fully qualify your range, Excel will assume that you are referring to the ActiveWorkbook and ActiveSheet which might not be your intention.

E.g. ThisWorkBook.Sheets("Sheet1").Range("B1:B" & LastRow)

Sub halveandcolorchange()
    
    Dim cell As Range, rng As Range, A As Range, LastRow As Long
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sales")

    LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row 
    Set rng = ws.Range("B1:B" & LastRow) 
    
    For Each cell In rng
        Set A = cell.Offset(0, -1)
        If cell.Value > 3 Then 
           A.Value = A.Value / 2
           A.Interior.ColorIndex = 3
        End If
    Next cell
End Sub

edited Aug 12 '21 at 15:41

answered Aug 12 '21 at 14:59

Raymond Wu

3,357
2
7
20

I've tried doing that but I get error 9. I've tried it with declarations and without (sheet 1 which I want I the changes on is called "sales" and the other sheet is called "marketing"). Sub halveandcolorchange() Dim cell As Range, rng As Range, A As Range, LastRow As Long LastRow = Cells(Rows.Count, "B").End(xlUp).Row Set rng = ThisWorkbook.Sheets("sales").Range("B1:B" & LastRow) For Each cell In rng Set A = cell.Offset(0, -1) If cell.Value > 3 Then A.Value = A.Value / 2 A.Interior.ColorIndex = 3 End If Next cell End Sub – Lysander2001 Aug 12 '21 at 15:27
@Lysander2001 Please edit your question with the updated code, no sane person can (or should) read those codes in comment. Which line do you get that Error 9? – Raymond Wu Aug 12 '21 at 15:29
Also tried this (got error 9): Sub halveandcolorchange() Dim Sa As Worksheet Dim cell As Range, rng As Range, A As Range, LastRow As Long Set Sa = ThisWorkbook.Worksheets("Sales") LastRow = Cells(Rows.Count, "B").End(xlUp).Row Set rng = Sa.Range("B1:B" & LastRow) For Each cell In rng Set A = cell.Offset(0, -1) If cell.Value > 3 Then A.Value = A.Value / 2 A.Interior.ColorIndex = 3 End If Next cell End Sub – Lysander2001 Aug 12 '21 at 15:31
Sorry about that. Here's the question: https://stackoverflow.com/questions/68760362/my-cannot-seem-to-specify-which-worksheet-to-perform-a-macro-whatever-i-do-i-g – Lysander2001 Aug 12 '21 at 15:34
@Lysander2001 You should edit your current question (the link to edit is just under the text of your question), not post a new one. Please double check that the sheet name is exactly "Sales", no extra space etc. – Raymond Wu Aug 12 '21 at 15:38
@Lysander2001 I have edited my answer to fit in your sheet name, as mentioned in my previous comment - Please make sure the sheet name is exactly "Sales", you will get that error because it can't find that worksheet so chances are the actual name has space in it. – Raymond Wu Aug 12 '21 at 15:42

Enact two different changes to cells (reduce value by 50% AND highlight red) in one sub

1 Answers1