Hello everyone
Im currently writing the code below (there's more stuff upwards and down but its not relevant to this section of code)
but now i've encountered an issue i don't know how to fix
i want to make a piece of code that analyzes from I3- AL and finds me the max and min value in each row
and then adds a gray arrow pointing upwards onto the cell that has the max value and a gray arrow pointing downwards on the min value cell in each row
on the bottom of the code there's 2 blocks of code,one that makes the background of the cells inside the specified range red if those cells are bigger than a specific value
and the other one makes the cell background blue if its smaller than the specific value
i want the arrows to be added regardless if the cell background is blue or red or doesn't have a background
I apologize if my post was not very easy to understand,if you have any questions ask away
thanks for reading
Im currently writing the code below (there's more stuff upwards and down but its not relevant to this section of code)
but now i've encountered an issue i don't know how to fix
i want to make a piece of code that analyzes from I3- AL and finds me the max and min value in each row
and then adds a gray arrow pointing upwards onto the cell that has the max value and a gray arrow pointing downwards on the min value cell in each row
on the bottom of the code there's 2 blocks of code,one that makes the background of the cells inside the specified range red if those cells are bigger than a specific value
and the other one makes the cell background blue if its smaller than the specific value
i want the arrows to be added regardless if the cell background is blue or red or doesn't have a background
Code:
Dim ws0 As Worksheet
Dim startRow As Long, endRow As Long
Dim rng0 As Range
Dim p As Long
Set ws0 = ThisWorkbook.Worksheets("Data")
startRow = 3 ' <--- nº da linha
endRow = ws0.Range("I" & ws0.Rows.count).End(xlUp).Row ' procura a ultima linha que tem um valor entre as colunas I até AL
' Clear existing conditional formatting
ws0.Cells.FormatConditions.Delete
' Executa um loop entre todas as linhas até chegar à ultima linha com um valor
For p = startRow To endRow
Dim lastCol As Long
lastCol = ws0.Cells(p, ws0.Columns.count).End(xlToLeft).Column ' procura a ultima coluna na linha que tenha um valor
If lastCol > ws0.Columns("AL").Column Then 'verifica se a ultima coluna está para além de AL
lastCol = ws0.Columns("AL").Column ' define a ultima coluna como a coluna AL se a estiver para lá de AL
End If
If WorksheetFunction.CountA(ws0.Range("I" & p & ":" & ws0.Cells(p, lastCol).Address)) > 0 Then 'verifica se a linha tem valores entre as colunas I até AL
Set rng0 = ws0.Range("I" & p & ":" & ws0.Cells(p, lastCol).Address) ' range da coluna I até à currentrow
' formatação condicional para o valor mais baixo
rng0.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=MIN(" & rng0.Address & ")"
With rng0.FormatConditions(rng0.FormatConditions.count)
.Font.Bold = True
.Interior.Color = RGB(255, 255, 0) ' cor amarela para o valor mais baixo
End With
' formatação condicional para o valor mais alto
rng0.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=MAX(" & rng0.Address & ")"
With rng0.FormatConditions(rng0.FormatConditions.count)
.Font.Bold = True
.Interior.Color = RGB(255, 165, 0) ' cor laranja para o valor mais alto
End With
End If
Next p
'Conditional Formatting
With Range(Cells(3, 9), Cells(RI, RJ)).FormatConditions.Add(xlCellValue, xlGreater, "=$G3") 'Maior que o limite
With .Font
.Bold = True
End With
With .Interior
.ColorIndex = 3
End With
End With
With Range(Cells(3, 9), Cells(RI, RJ)).FormatConditions.Add(xlCellValue, xlLess, "=$H3") 'Menor que o limite
With .Font
.Bold = True
End With
With .Interior
.ColorIndex = 23
End With
End With
I apologize if my post was not very easy to understand,if you have any questions ask away
thanks for reading