Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to colored above row will Automatically when I will enter last row

0

Sir,

I have 10 ten rows. When I click A3; after entered data in row no.2 [from column A to E ], the above row like row no.2 will coloured automatically. How can I colored above row will Automatically when I will enter last row.

File attached for your kind reference.

Thank you.

Answer
Discuss

Discussion

Naga

This is a Q&A forum but you haven't really asked a question! Please EDIT your original question to say "How can I..." and specify is you want the row to be coloured only when columns A-E have values, whether it's the cell colour or the font colour you need to change (and to which colour). I assume you mean this to happen for any row.
John_Ru (rep: 6142) Nov 22, '21 at 10:10 am
Naga

Forget the above- I guessed and gave you an asnwer. Please write questions carefully in future and be sure to describe what your problem is. We normally expect you to have tried some appraoches before you ask for a fix.
John_Ru (rep: 6142) Nov 22, '21 at 10:43 am
Add to Discussion

Answers

0
Selected Answer

Naga

The code below (and in the attached file) will colour A:E of a row in Sheet1 if the cell below that row but in column A is clicked. I've added comments to the code so you can understand how it works hopefully (it uses an event macro which is triggered when the selected cell is changed): 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myColumn As Long
' Do nothing unless a cell in A was picked, row 2 or below
If Target.Row = 1 Or Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
' Loop through A:E above cell and do nothing if any has no value
For myColumn = 1 To 5
    If Cells(Target.Row - 1, myColumn).Value = "" Then Exit Sub
Next myColumn
'If all complete above, colour the cells green
Target.Offset(-1, 0).Resize(1, 5).Interior.Color = vbGreen

End Sub
Hope this is what you wanted (and in future, please try to provide full questions).

Note that vBGreen is one of a few "Color constants" in VBA, you can't just change the Green bit for any colour but using the .ColorIndex property instead of .Color would give you a choice of 57 colours. You could also change the last line of the code to read:

Target.Offset(-1, 0).Resize(1, 5).Interior.Color = RGB(0,255,255)
to set RGB colours (and adjust the Red, Green and Blue numbers to get very many colours). The 3 bold numbers stated above would give cyan for example.

Hope this helps.

Discuss

Discussion

Naga. Did that work for your purposes?
John_Ru (rep: 6142) Nov 27, '21 at 6:08 am
Thank u John.

and sorry to that.
Naga (rep: 10) Nov 27, '21 at 8:01 am
No problem Naga. Glad it worked for you.
John_Ru (rep: 6142) Nov 27, '21 at 8:25 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login