Forum Discussion

Jenstarzie's avatar
Jenstarzie
Copper Contributor
Apr 22, 2025

VBA Coding to hide/unhide a row based on a cells colour

Hi,

I'm fairly new to VBA and just getting to grips with some basic functions but am struggling with a slightly more complex one that i would like to implement. I have searched around but can't find an answer to my specific problem so hoping someone here will be able to point me in the right direction please. 

I have an excel workbook with multiple sheets. Lets call them Sheet1 and Sheet2. 

I currently have a conditional format set so that if cellA1 in Sheet2 is Red, then cellA2 in Sheet1 auto formats to red and any text in cellB1 in Sheet2 is brought forward to cellB2 in Sheet1 (via in cell formula, not VBA) with row2 of sheet1 in standard position of hidden. 

I would like to add a string into VBA so that when cell A2 of sheet1 auto formats to Red, row2 sheet1 unhides, but any other value/colour present would keep row2 hidden.  

A few questions around this: 

  1. Is this even possible?
  2. Do i need to move the colour formatting into VBA instead of an in cell formula and conditional formatting, and if yes, how do i do that? 

So far my VBA experience is mostly just straightforward 'IF' functions and my knowledge is very basic so i would be very grateful of any advice and suggestions you have with clear descriptions so i can understand and learn. 

Thank you in advance :) 

excel
Formulas and Functions
Macros and VBA
office 365

2 Replies

Sort By
  • Jenstarzie's avatar
    Jenstarzie
    Copper Contributor
    Apr 23, 2025

    Thank you for the reply. Could you please explain this code to me so i understand how it works? 

     

    Thank you 

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Apr 23, 2025

    How about this:

     

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
    Dim cellColor As Long
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your actual sheet name
    
    ' Check if the modified cell is A2
    If Not Intersect(Target, ws.Range("A2")) Is Nothing Then
        ' Get the interior color of cell A2
        cellColor = ws.Range("A2").Interior.Color
        
        ' Check if the color matches Red (adjust RGB value as needed)
        If cellColor = RGB(255, 0, 0) Then
            ws.Rows(2).Hidden = False ' Unhide row 2
        Else
            ws.Rows(2).Hidden = True ' Hide row 2
        End If
    End If
End Sub

     

Resources