Hello everyone, I am trying to create something like the attached image below. There are 4 conditions.
The first, I want to automatically highlight any value in column D that is greater than 1.5 and in turn highlight the corresponding rows in columns B and C. I've already managed do half of it, but I need to input text to the right of it saying "Skew too high, don't use" and the rows to be highlighted as well.
Next, I need to set upper and lower limit values in column C and add corresponding text to it on the right side if the upper and lower limit conditions aren't met. In this example, any value greater than 574 (and the corresponding rows in columns B and C) will need to be highlighted in yellow and the text: "TD too high, scrap" to appear to the right of it. Any value lower than 554 (and the corresponding rows in columns B and C) will also need to be highlighted in yellow and the text should say: "TD too low, scrap" to the right of it.
Lastly, any value that does not have a pair, in this case #111 in column B will also need to be highlighted in yellow and the text: "Unmatched" to be written to the right of it.
I will attach my code and show you what I have so far. This code includes a few different operations in it that I've added to one macro button. Is it possible to do something like this? If so, can someone help me get there? Thank you
The first, I want to automatically highlight any value in column D that is greater than 1.5 and in turn highlight the corresponding rows in columns B and C. I've already managed do half of it, but I need to input text to the right of it saying "Skew too high, don't use" and the rows to be highlighted as well.
Next, I need to set upper and lower limit values in column C and add corresponding text to it on the right side if the upper and lower limit conditions aren't met. In this example, any value greater than 574 (and the corresponding rows in columns B and C) will need to be highlighted in yellow and the text: "TD too high, scrap" to appear to the right of it. Any value lower than 554 (and the corresponding rows in columns B and C) will also need to be highlighted in yellow and the text should say: "TD too low, scrap" to the right of it.
Lastly, any value that does not have a pair, in this case #111 in column B will also need to be highlighted in yellow and the text: "Unmatched" to be written to the right of it.
I will attach my code and show you what I have so far. This code includes a few different operations in it that I've added to one macro button. Is it possible to do something like this? If so, can someone help me get there? Thank you
VBA Code:
Sub SortCol()
Dim WS As Worksheet
Dim rng As Range
Set WS = ThisWorkbook.Worksheets("Data")
With WS
Set rng = .Range("B4:C4" & .Range("C" & .Rows.Count).End(xlUp).Row)
End With
rng.Sort Key1:=rng.Columns(2), Order1:=xlAscending, Header:=xlYes
Call Macro2
Call HighlightRangeOfCells
Call PrintArea
'PrintSheet
End Sub
Sub Macro2()
Range("B1:D1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("F2").Select
End Sub
Sub HighlightRangeOfCells()
Dim rng As Range
For Each rng In Range("D5:D93")
If IsNumeric(rng.Value) Then
If rng.Value > 1.5 Then
rng.Interior.Color = vbYellow
End If
End If
Next rng
End Sub
Sub PrintArea()
Dim lr As Long
lr = Range("B:C").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$" & lr
End Sub
Sub PrintSheet()
Dim WS As Worksheet
Set WS = ThisWorkbook.Worksheets("Data")
WS.PrintOut
End Sub