VB macro on Excel

[craggers]

I am trying to write VB macro for Excel.

Basically what I want to do if the user enter 'X' in one of the cell and the whole of the row will be highlighted to green automatically.

What kind of code will this work?

[2something]

Before you embark on the macro route, maybe check whether conditional formatting can be used to do this for you.

-> Format
-> Conditional formatting
-> bit of ingenuity


i.e. you can have Cell Value 'not equal' to an emtpy cell.
If the cell is empty - no formatting
if the cell isn't empty - formatting

etc.

sorry, just read you want the whole row, in this case you do one conditional formatting on the input cell that it shouldn't equal an empty cell, then you do a conditional formatting on the rest of the row that all the cells shouldn't equal the input cell of the row . Take care though as you need to make sure calculation is set to automatic, else you need F9 to update the colouring. If you want the user to be able to input something into any cell in the row and then have the whole row update colour, vb is probably called for ...

Edited by 2something on Tuesday 22 May 11:55

[Don]

You can do this easily.

You just need a For Next loop going through the columns for a particular row setting the colour.

Easiest way to find out how to set the colour is to "record" a macro that does what you want. This actually generates VB that you can then start playing about with adding loops and so on.

If all that sounds way too hard see if any of your organisation's IT guys could help you for an hour. That's all it should take.

[nickd01]

Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If UCase(Target) = "X" Then
Rows(Target.Row & ":" & Target.Row).Select
With Selection.Interior
.ColorIndex = 50
.Pattern = xlSolid
End With
End If
Target.Select
End Sub

Not the best or neatest code, but should do what you want. Paste it in ThisWorkbook

[craggers]

nickd01 said:

Try this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If UCase(Target) = "X" Then
Rows(Target.Row & ":" & Target.Row).Select
With Selection.Interior
.ColorIndex = 50
.Pattern = xlSolid
End With
End If
Target.Select
End Sub

Not the best or neatest code, but should do what you want. Paste it in ThisWorkbook

Work perfectly !!! Is it possible to add columns one too under "Row(Target. Row ...."

[nickd01]

All I'm doing is selecting a range - it just happens to be a row.

You can select a whole column using Range("C:C" for example.

Just record a macro, select the column, stop recording then look at the code. Your profile says you're a programmer so I'm sure you'll work the rest out

[craggers]

nickd01 said:

All I'm doing is selecting a range - it just happens to be a row.

You can select a whole column using Range("C:C" for example.

Just record a macro, select the column, stop recording then look at the code. Your profile says you're a programmer so I'm sure you'll work the rest out

That right, recently moved job and not done the VB macro for years !! I'm C++ man.

[craggers]

Managed to do row or column at seperate but can't get both to work (row and column to be highlighted together when pressing "X' on one of the cell)

VB is weird tool

[nickd01]

craggers said:

Managed to do row or column at seperate but can't get both to work (row and column to be highlighted together when pressing "X' on one of the cell)

VB is weird tool

Ok, ok (I hate C++ so I understand your frustration!)

Something like this (If I understand what you're trying to do):

If UCase(Target) = "X" Then
Rows(Target.Row & ":" & Target.Row).Select
With Selection.Interior
.ColorIndex = 50
.Pattern = xlSolid
End With
Dim strCol
strCol = Mid(Target.Address, 2, 1)
Columns(strCol & ":" & strCol).Select
With Selection.Interior
.ColorIndex = 50
.Pattern = xlSolid
End With
End If
Target.Select

[ATG]

Assuming your Excel has the full set of help files installed, highlight Range or Row or Column ... any method that returns a Range object will do ... and press F1 to open the help files. If you poke around for a few seconds, you should get the documentation for the Excel "range" object model and that should start giving you some clues for this type of stuff.

[craggers]

nickd01 said:

craggers said:

Managed to do row or column at seperate but can't get both to work (row and column to be highlighted together when pressing "X' on one of the cell)

VB is weird tool

Ok, ok (I hate C++ so I understand your frustration!)

Something like this (If I understand what you're trying to do):

If UCase(Target) = "X" Then
Rows(Target.Row & ":" & Target.Row).Select
With Selection.Interior
.ColorIndex = 50
.Pattern = xlSolid
End With
Dim strCol
strCol = Mid(Target.Address, 2, 1)
Columns(strCol & ":" & strCol).Select
With Selection.Interior
.ColorIndex = 50
.Pattern = xlSolid
End With
End If
Target.Select

Work nice but only problem this won't work if the column is AA onward (only work from A-Z)

[ATG]

Tip: never use strings to describe ranges in VBA; use integers.
For example:
Set oRange = Worksheet("Some Sheet" ).Cells(1,1)
to get a reference to a range object pointing to cell "a1"

If you want the entire column, do something like:
Set oRange = Worksheets("Some Sheet" ).Columns(1)

If you want a block of cells:
With Worksheets("Some Sheet" )
Set oRange = .Range(.Cells(r1,c1), .Cells(r2,c2))
End with

where r1,r2,c1,c2 are all integers, and (r1,c1) and (r2,c2) are coords of the cells in opposite corners of the range you want

Edited by ATG on Tuesday 22 May 15:11