How to add an arrow onto a cell? (1 Viewer)

[goncalo]

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

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

 

[The_Doc_Man]

The way I added arrows was to determine the cell to be highlighted that way, then find the adjacent empty cell and add a character selected from the WindDings3 font.

    If sToPg <> "" Then                 'identified a link?
        With xWkSh2.Range("F4")         'yes, prepare to fill it in
            .Value = Chr$(&HE2)         'horizontal right arrow
            .Font.Name = "Wingdings 3"
            .Font.Color = vbBlack
            .Font.Size = 12
        End With

In this case, I was using a fixed layout so could predict that IF the arrow was needed, it would appear in cell F4 of that page. You have several good sets of arrows in WingDings3 - up, down, left, right, and the four diagonals. The only difference is you would have to identify the cell to get the arrow, pick the arrow you really wanted to use to load to the value, and change the WITH statement to select that particular cell.