Loop through each cell in a given range and change the value depending on value in a column in the same row

0 votes

As an illustration of the conditions, I have the code below that modifies a value in the H2–K2 range. Nevertheless, I need it to iterate over each of the coloured cells.

Choose all of the coloured cells to set the range, then compare it to the numbers in the "Product" column.

But, each time the macro is executed, the range of coloured cells will differ, and the value to be verified (Column P) won't always be in Column P.

No change has to be performed to the cell if the value does not satisfy any of the requirements in the If statement.

Spreadsheet

Any help would be much appreciated

Sub Turn_Live_Digital()

If Range("p2").Value = "ADSHEL LIVE" Or _
   Range("p2").Value = "ASDA LIVE" Or _
   Range("p2").Value = "CC DIGITAL MALLS" Or _
   Range("p2").Value = "CCB DIGITAL" Or _
   Range("p2").Value = "MALLS XL" Or _
   Range("p2").Value = "SAINSBURYS LIVE" Or _
   Range("p2").Value = "SOCIALITE" Or _
   Range("p2").Value = "STATION LIVE" Or _
   Range("p2").Value = "STORM DIGITAL" _
Then
    Range("h2:k2").Value = "Digital"
End If
End Sub

I wouldn't know where to start

Mar 26, 2023 in Others by narikkadan
• 63,420 points
539 views

1 answer to this question.

0 votes

Use match() and if() without code at all, like so:

IF(IFERROR(MATCH(C3,A4:A12,0),0)>0,"Digital","")

The iferror converts to 0 if the text in C3 is null, and the if() prints "Digital" if the result is higher than 0. The list structure makes it, in my opinion, simpler to add or amend than to deal with coding modifications.

enter image description here

answered Mar 26, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

Select a range in Excel based on the value of a cell

Try this: Dim WorkRng As Range Set WorkRng = ...READ MORE

answered Jan 29, 2023 in Others by narikkadan
• 63,420 points
472 views
0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,420 points
596 views
0 votes
1 answer

VBA Loop to select then copy a range of cells based on value in column B

Try this: Sub Macro2() Dim ...READ MORE

answered Mar 23, 2023 in Others by narikkadan
• 63,420 points
1,714 views
0 votes
1 answer

Get random value in the range of plus/minus 10% of a cell value in Excel

Why not just use RANDBETWEEN(B2*0.9, B2*1.1) if ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,420 points
469 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
916 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,240 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
532 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
769 views
0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,010 points
1,189 views
0 votes
1 answer

Excel VBA: Obtain the Column and Row of current element in "For Each" loop

Try this: MsgBox rng.Address(RowAbsolute:=False, ColumnAbsolute:=F ...READ MORE

answered Feb 14, 2023 in Others by Kithuzzz
• 38,010 points
789 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP