Change cell colour conditional to position

[keysol]

Hi,
First off I'm a true newbie regarding VBA etc.

If I create a grid containing 28 columns and 19 rows, I would like to be able to enter the letter "A" in a cell somewhere in this grid and have excel colour green the cell where I entered the letter and then colour the rest of the column black.
I.E.: The column is L10:L28 and the user enters the letter "A" at cell L16. Cell L16 turns green and cells L17:L28 turn black.

Any help would be appreciated

 

[MickG]

Try this:-
Place code in worksheet module:= Right click sheet tab, select "view Code", Code window appears , paste code.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Set Rng = Range("A10").Resize(19, 28)
If Not Intersect(Rng, Target) Is Nothing Then
    If UCase(Target) = "A" Then
        Rng.Interior.ColorIndex = xlNone
        Target.Interior.ColorIndex = 4
        If Not Target.Row = 28 Then Target.Offset(1).Resize((Rng.Rows.Count + 9) - (Target.Row)).Interior.ColorIndex = 1
    End If
End If
End Sub

 

[keysol]

Hi MickG,

Thanks for responding to my post.
I tried your suggestion without any luck. Nothing seems to happen. Please remember I am truly new to this so I might be missing a very basic point when it comes to incorporating the code into my spreadsheet. Unfortunately I'm unable to attach a screen shot. I right clicked to view code and pasted the code in "ThisWorkbook" The top fields of the window were "Workbook" and "Open". I don't know if this helps.

Regards,

Gerald

 

[MickG]

When you right click the sheet tab and select View code, The VB Code Window will appear, there should be nothing in the code window unless in you have written any previous code for that particular sheet. The only thing you might see is the words "Option Explicit". In that blank window is where you paste the code, Just Right click in the window and select "Paste".
You will then see the code as I sent it.
Close the Vb window and place an "A" somewhere in your Range . Click return and you column should be Coloured.

 

[keysol]

You're right, I already have some code in that window. When I remove the existing code yours works.
One problem is that when I input another "A" elsewhere in the grid the black coloured cells in the first column revert back to white and the new column turns black below the green "A".
Also I wish to keep the existing code that I already had in the spreadsheet.

Gerald

 

[MickG]

To keep the colours remove this line:-"Rng.Interior.ColorIndex = xlNone"
You can also keep your other code as long as it is not a "Worksheet _Change" Event.

 

[keysol]

Hi again MickG,

I thought I'd drop you a line to thank you again for your help. As I said in a previous post when I use your code on its own it works perfectly.
Because I have another snippet it's causing me grief. I realize I need to acquire a basic understanding of VBA otherwise I'm going at this blindly. I've started viewing Youtube tutorials to get there.

Regards,

Gerald

To keep the colours remove this line:-"Rng.Interior.ColorIndex = xlNone"
You can also keep your other code as long as it is not a "Worksheet _Change" Event.