decadence
Well-known Member
- Joined
- Oct 9, 2015
- Messages
- 525
- Office Version
- 365
- 2016
- 2013
- 2010
- 2007
- Platform
- Windows
Hi I am trying to compare cells in 2 columns and return the value in the 3rd column on the same Row.
This is what I have so far, which is the Ranges I need to use, and have several criteria
Compare each cell between Rng1 and Rng2 and if return the value in the xRng Column if:
-the cell Value in Rng1 is below number 1 then hide that row
-the same number between Rng1 and Rng2 then returned value in xRng column as "0" and turn xRng cell Blue
-the difference is greater in Rng1 than Rng then return the difference and turn xRng cell Red
-the difference is Smaller in Rng1 than Rng then return the difference and turn xRng cell Green
Can someone help with this please
Example
<tbody>
</tbody>
To
<tbody>
</tbody>
This is what I have so far, which is the Ranges I need to use, and have several criteria
Compare each cell between Rng1 and Rng2 and if return the value in the xRng Column if:
-the cell Value in Rng1 is below number 1 then hide that row
-the same number between Rng1 and Rng2 then returned value in xRng column as "0" and turn xRng cell Blue
-the difference is greater in Rng1 than Rng then return the difference and turn xRng cell Red
-the difference is Smaller in Rng1 than Rng then return the difference and turn xRng cell Green
Can someone help with this please
Code:
Option Explicit
Public Fnd As Range, Rng1 As Range, Rng2 As Range, xRng As Range, xVal1 As Range, xVal2 As Range, xVal3 As Range
Sub Total()
If Range("A1").End(xlToRight) = "Total" Then Exit Sub
Range("A1").End(xlToRight).Offset(, 1) = "Total"
Set Rng1 = RngReq
Set Rng2 = RngIss
Set xRng = RngTotal
For Each xVal1 in Rng1
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Compare Columns and Return value
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Next xVal1
End Sub
Function RngReq() As Range
Set Fnd = ActiveSheet.Columns.Find(What:="RequiredQty", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not Fnd Is Nothing Then
Set RngReq = Range(Fnd.Offset(1), Cells(Rows.count, Fnd.Column).End(xlUp))
End If
End Function
Function RngIss() As Range
Set Fnd = ActiveSheet.Columns.Find(What:="IssuedQty", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not Fnd Is Nothing Then
Set RngIss = Range(Fnd.Offset(1), Cells(Rows.count, Fnd.Column).End(xlUp))
End If
End Function
Function RngTotal() As Range
Set Fnd = ActiveSheet.Columns.Find(What:="Total", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not Fnd Is Nothing Then
Set RngTotal = Range(Fnd.Offset(1), Cells(Rows.count, Fnd.Column).End(xlUp))
End If
End Function
Example
Rng1 Column | Rng2 Column | Some Column | Other Column | xRng Column |
60 | 300 | |||
100 | 0 | |||
0.001 | 0 | |||
20 | 20 |
<tbody>
</tbody>
To
Rng1 Column | Rng2 Column | Some Column | Other Column | xRng Column |
60 | 300 | 240 (Green) | ||
100 | 0 | 100 (Red) | ||
20 | 20 | 0 (Blue) |
<tbody>
</tbody>