SOLVED

Question related highlight cell with the help of VBA code

%3CLINGO-SUB%20id%3D%22lingo-sub-2687854%22%20slang%3D%22en-US%22%3EQuestion%20related%20highlight%20cell%20with%20the%20help%20of%20VBA%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2687854%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3EI%20want%20to%20highlight%20cells(Red%20colour)%20which%20is%20more%20than%204%20in%20SPACE%20COLUMN%20with%20the%20help%20of%20VBA%20code.%3C%2FP%3E%3CP%3ESo%2C%20what%20should%20i%20write%20in%20VBA%20code%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help..%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20a%20attached..%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2687854%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2688083%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20highlight%20cell%20with%20the%20help%20of%20VBA%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2688083%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20easily%20use%20conditional%20formatting%20for%20this.%20You%20wouldn't%20need%20VBA%2C%20and%20the%20formatting%20would%20be%20updated%20automatically%20when%20the%20values%20in%20column%20D%20change.%3C%2FP%3E%0A%3CP%3EBut%20if%20you%20really%20want%20to%20use%20VBA%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20ColorCells()%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20m%20%3D%20Range(%22D%22%20%26amp%3B%20Rows.Count).End(xlUp).Row%0A%20%20%20%20Range(%22A2%3AD%22%20%26amp%3B%20m).Interior.ColorIndex%20%3D%20xlColorIndexNone%0A%20%20%20%20For%20r%20%3D%202%20To%20m%0A%20%20%20%20%20%20%20%20If%20Range(%22D%22%20%26amp%3B%20r).Value%20%26gt%3B%204%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(%22A%22%20%26amp%3B%20r).Resize(1%2C%204).Interior.Color%20%3D%20vbRed%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20r%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Regular Contributor

Hello Everyone,

I want to highlight cells(Red colour) which is more than 4 in SPACE COLUMN with the help of VBA code.

So, what should i write in VBA code?

 

Please help..??

 

Here is a attached..

6 Replies

@Zan_Hanifee 

You can easily use conditional formatting for this. You wouldn't need VBA, and the formatting would be updated automatically when the values in column D change.

But if you really want to use VBA:

Sub ColorCells()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("D" & Rows.Count).End(xlUp).Row
    Range("A2:D" & m).Interior.ColorIndex = xlColorIndexNone
    For r = 2 To m
        If Range("D" & r).Value > 4 Then
            Range("A" & r).Resize(1, 4).Interior.Color = vbRed
        End If
    Next r
    Application.ScreenUpdating = True
End Sub
Why would you want to use VBA for this? A simple conditional format would suffice.
Sir, if we highlight only SPACE column, then what should i write in VBA?
Hello Sir,
Actually i am learning VBA program.
best response confirmed by Zan_Hanifee (Regular Contributor)
Solution

@Zan_Hanifee 

Change the line

            Range("A" & r).Resize(1, 4).Interior.Color = vbRed

to

            Range("D" & r).Interior.Color = vbRed

Remark: if you want to learn to write VBA, you should really try to make such changes yourself. Even if you make mistakes, you will learn much more from experimenting than from asking us for every small detail.

 

@Hans Vogelaar 

Got it sir. 

Thank you very much sir.