1

as said above, i would like to highlight the background of certain cells from another column based on the text that is on the "example" picture for example : if the text is "Run" it highlights the cell as green

open for any other ways to do it

will reply to any questions thank you for your time

example

Miguel Ramos
  • 11
  • 2
  • You can see this answer : https://stackoverflow.com/questions/1520429/is-there-a-css-selector-for-elements-containing-certain-text – Virinas-code Jun 14 '21 at 13:52
  • 4
    You don't need VBA to do this. Conditional formatting using a simple formula will work. – BigBen Jun 14 '21 at 13:54

2 Answers2

1

This URL explains how conditional formatting can do this.

The question is, why did you opt for VBA for doing this:

  • You were not aware of conditional formatting.
  • You only want this do be done, based on a certain action (clicking a button).

In the second case, highlighting can be done as follows (based on a selected cell):

    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
Dominique
  • 16,450
  • 15
  • 56
  • 112
  • i choose vba because when i searched for this, the conditional formatting examples were only using 1 column and not another value to condition the color of another one – Miguel Ramos Jun 15 '21 at 07:53
  • Oh, in that case I would definitely recommend conditional formatting: it can be used over different cells, even over different worksheets. – Dominique Jun 15 '21 at 08:26
  • i will give it a search, thank you so much! – Miguel Ramos Jun 15 '21 at 08:28
0

Highlight Cells

  • The following assumes that the A column contains the criteria (Run) and the B column is formatted. Adjust this to fit your needs.

Excel

In e.g. cell B2 use the conditional formatting rule (Use a formula to determine which cells to format)...

=A2="Run"

... and copy down.

VBA

  • Adjust the values in the constants section and the worksheet (Something like Set ws = Thisworkbook.Worksheets("Sheet1") is preferred (safer).).
Option Explicit

Sub HighlightCells()
    
    Const sCol As String = "A"
    Const sFirst As Long = 2
    Const sCriteria As String = "Run"
    
    Const dCol As String = "B"
    Const dColor As Long = vbGreen
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    
    Dim sLast As Long: sLast = ws.Cells(ws.Rows.Count, sCol).End(xlUp).Row
    If sLast < sFirst Then Exit Sub
    
    Dim r As Long
    Dim sString As String
    
    For r = sFirst To sLast
        sString = CStr(ws.Cells(r, sCol).Value)
        If sString = sCriteria Then
            ws.Cells(r, dCol).Interior.Color = dColor
        End If
    Next r

End Sub

Conditional Formatting in VBA

(a third option)

VBasic2008
  • 44,888
  • 5
  • 17
  • 28