How to take a piece of spreadsheet to a sniped code and translate into array form
Category: [Excel] Demo Available
How to take a piece of spreadsheet to a sniped code and translate into array form
Requirement:
The user is trying to grab the path from a piece of spreadsheet to a sniped code in order to understand how to translate into array form and looping. The user wants just to compare 3 numbers at B2, C2, D2 against 9 numbers located at (G1,H1,I1) (G2, H2,I2) and (G3,H3,I3) IF any number on BCD is on GHI highlight on GHI.
On range(G2:I4) the user wants to search for the number on the range(B2:D2).
https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1218795-vba-array-and-loop
Solution:
Code:
Sub ColorCells()
Application.ScreenUpdating = False
Dim rng As Range, fnd As Range
For Each rng In Range("B2:D2")
Set fnd = Range("G2:I4").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
If Not fnd Is Nothing Then
fnd.Interior.ColorIndex = 6
End If
Next rng
Application.ScreenUpdating = True
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Mumps.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA code to generate report based on criteria |
| How to use Excel VBA code to hide based on criteria |
| How to code change on moving cell contents down one |
| How to protect VBA source code from view |
| How to use VBA Code in Excel to display a balloon |
| How to use VBA code to clear cells based on specific criteria |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.