VBA command to fill an excel row with a color 2

[mrtauntaun]

Hi all. I am looking to do the following:

If AD2 = 0 then 'Fill row 2 with color(red)'

Either the entire row or a selection of cells in that row, whichever is easier. I've googled like crazy, but I can't find this command. Thanks!

 

[ThomasLafferty]

You don't need VBA. Look into Format>Conditional Formatting.

Select row 2, and set it to Expression and enter the formula:
=$A$2=0

Hope this helps.

Tom

Live once die twice; live twice die once.

 

[ThomasLafferty]

Oops!
It should have been:
=$A[!]D[/!]$2 = 0

Cheers!

Tom

Live once die twice; live twice die once.

 

[mrtauntaun]

That's cool. To take this a step further, what if I wanted it to check a range of cells? Say, AD2 through AD10?

 

[mrtauntaun]

Also, this spreadsheet will be generated daily, based on a criteria of code. So I can't set it every day by hand, which is why I wanted to use code.
Is there a VBA alternative?

 

[SkipVought]

Hi,

I work with alot of workbooks that are generated on a schedule.

I have a workbook that has a QueryTable in a sheet and all I do is either simply Data/Refresh if the filename does not change or, run a simple query to format the connect string for the new filename (usually formatted for a date).

All the sheet formatting and functionality is in the receiving workbook. The souce workbook remains untouched. It's a pretty simple approch and would lend itself to Conditional Formatting as described above.

Skip,
[sub]
[red][/red]
[/sub]

 

[AnotherHiggins]

Turn on the macro recorder (Tools > Macro > Record new macro) then go through the steps of Conditional Formatting. Stop recording. Observe code that was generated ([Alt] + [F11] opens the VB Editor). Your code solution will be staring at you.

BTW - Conditional Formatting can easily be used on a range - even the entire column if you wish. Look at Excel's help file on Conditional Formatting and on Absolute vs Relative References.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[ThomasLafferty]

Sure! Use a loop like this:

Sub ConditionallyFormatCells()
'*****Variable Declarations
Dim CellVal, Target As Range

'Initialize Variable
CellVal = 0

For CheckVal = 1 To 9 'evaluate 9 cells
     Cells(CheckVal + 1, 30).Select
     Set Target = ActiveCell
     CellVal = CellVal + Target.Value
NextCheckVal

'Check to see if the sum of all values is greater than 0
     If CellVal>0 Then
          Range("2:2").Select
               With Selection.Interior
                    .ColorIndex = 3
                    .Pattern = xlSolid
               End With
     End if
End Sub

Hope this helps...
Tom

Live once die twice; live twice die once.