Compares two columns based on the value of the third column value
I want to create a macro to look at column (AF) and based on that value, compare column (BI), (BJ) and / or (BK) together, and if its false, highlight the compared yellow cells. I know it's a little hard to follow, but this example should help clarify:
My sheet has the following columns:
Column AF Column BI Column BJ Column BK
PRODUCT Height Length Width
I need a macro to see the product type and compare sizes for that product as follows:
- If product = A, then Length = Width, if not then highlight Length and Width Cells
- If product = B then Length > Width, if not then highlight Length and Width Cells
- If product = C then Width > Height < Length, if not highlight Length, Width, and Height cells
- If product - D then Width = Length < Height, if not highlight Width, Length, and/or Height
My data starts on line 3 and ends on line 5002.
I tried looking into this and was able to find solutions that compare two cells and then write the third column. I could combine an IF formula and conditional formatting to achieve this, but I don't want this trigger to run all the time the sheet is sorted and color coded. I am planning to put this macro in a command button.
source to share
Suggest to combine Statements
, for example Select Case
, If...Then...Else
together with carriers And
, Or
. See the following pages:
https://msdn.microsoft.com/en-us/library/office/gg251599.aspx
https://msdn.microsoft.com/en-us/library/office/gg278665.aspx
https://msdn.microsoft.com/EN-US/library/office/gg251356.aspx
After that, you should be able to write something similar to this: (The code below is just a sample, it won't work)
Select Case Product
Case A
If Length <> Width Then
Rem Highlight Length And Width Cells
End If
Case B
If Length <= Width Then
Rem Insert here the code to highlight Length And Width Cells
End If
Case C
If Width <= Height And Height >= Length Then
Rem Insert here the code to highlight Length, Width, and Height cells
End If
Case D
If Width <> Length And Length >= Height Then
Rem Insert here the code to highlight Width, Length, and/or Height
End If
End Sub
If you do not know to highlight the cells of the width, length and height; I suggest doing this manually when recording the macro, it will give a good starting point.
source to share
I suggest working with objects, defining variables for a range of data, checking each row, position of fields to check, etc. see below code with comments
Sub Highlight_Cells_based_Comparison()
Dim rData As Range
Dim rRow As Range
Dim rCllsUnion As Range
Rem Set variables to hold Fields position within the DATA range
Dim bPosProd As Byte, bPosHght As Byte, bPosLeng As Byte, bPosWdth As Byte
Rem Set variables to hold Fields values
Rem (data type Variant as don't know type of values these fields are holding, change as appropriated)
Rem see https://msdn.microsoft.com/en-us/library/office/gg251528.aspx)
Dim sProd As String, vHght As Variant, vLeng As Variant, vWdth As Variant
Dim lRow As Long
Rem Set Range (assuming it goes from column C to BK - change as needed)
Rem Not starting from column A on porpuse
Set rData = ActiveSheet.Range("C3:BK5002")
Rem Get Fields position from Header row
Rem Suggest to use this method instead of hard coding columns
On Error Resume Next
With rData
bPosProd = WorksheetFunction.Match("PRODUCT", .Rows(1), 0)
bPosHght = WorksheetFunction.Match("Height", .Rows(1), 0)
bPosLeng = WorksheetFunction.Match("Length", .Rows(1), 0)
bPosWdth = WorksheetFunction.Match("Width", .Rows(1), 0)
End With
If Err.Number <> 0 Then Exit Sub
On Error GoTo 0
Rem Loop thru each row excluding header
For lRow = 2 To rData.Rows.Count
Set rRow = rData.Rows(lRow)
With rRow
Rem Get Row Field values
sProd = .Cells(bPosProd).Value2
vHght = .Cells(bPosHght).Value2
vLeng = .Cells(bPosLeng).Value2
vWdth = .Cells(bPosWdth).Value2
Select Case sProd
Case A 'Change value of A as required
Rem If product = A, then Length = Width, if not then highlight Length and Width Cells
Rem If Length <> Width Then Highlight Length And Width 'Cells
If vLeng <> vWdth Then
Set rCllsUnion = Union(.Cells(bPosLeng), .Cells(bPosWdth))
Rem Suggest to use a subroutine for this piece as it a repetitive task
Rem see https://msdn.microsoft.com/en-us/library/office/gg251648.aspx
GoSub CllsUnion_Highlight
End If
Case B
Rem repeat as in Case A with required changes
Case C
'...
Case D
'...
End Select: End With: Next
Exit Sub
Rem Subroutine to highlight cells
CllsUnion_Highlight:
With rCllsUnion.Interior
.Color = 65535
.TintAndShade = 0
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.PatternTintAndShade = 0
End With
Return
End Sub
source to share