change row colour when a value is given in a cell

Hi,

I dont have that great knowledge in Excel but i wanted to try and run a
macro in an IF formula but realised that was not possible. I have now got a
formula that gives a value in a cell when i want the whole row to turn to
red, but im not sure how i can get a macro that will change the whole row to
red when that cell shows a value. Can anyone help on this matter?

Thanks in advance,

Alex

Sub colorrow()
If Cells(1, 2) = 3 Then Cells(1, 2).EntireRow.Interior.ColorIndex = 6
End Sub
--
Don Guillett
SalesAid Software
[email protected]
"Alex Simpson" <Alex [email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I dont have that great knowledge in Excel but i wanted to try and run a
> macro in an IF formula but realised that was not possible. I have now got
> a
> formula that gives a value in a cell when i want the whole row to turn to
> red, but im not sure how i can get a macro that will change the whole row
> to
> red when that cell shows a value. Can anyone help on this matter?
>
> Thanks in advance,
>
> Alex

Conditional formatting is your friend

Say you want the entire row to change color (colour) if there is an entry in
column A. First, select all the rows you want to format this way, keeping
row 1 active. Then use Format>Conditional Formatting.

In the dialog box that appears, select Formula is and then type in

=not(isblank($a1))

then click on the Format button, select the Patterns tab, and choose the
color you want to use. OK your way back out to the spreadsheet and try
entering values in column A


"Alex Simpson" wrote:

> Hi,
>
> I dont have that great knowledge in Excel but i wanted to try and run a
> macro in an IF formula but realised that was not possible. I have now got a
> formula that gives a value in a cell when i want the whole row to turn to
> red, but im not sure how i can get a macro that will change the whole row to
> red when that cell shows a value. Can anyone help on this matter?
>
> Thanks in advance,
>
> Alex

Hi, thank you very much for the information on conditional formatting. I did
everything exactly as you said but the cells dont change colour when i put in
a value or leave it blank it makes no difference.

Thanks,

Alex

"Duke Carey" wrote:

> Conditional formatting is your friend
>
> Say you want the entire row to change color (colour) if there is an entry in
> column A. First, select all the rows you want to format this way, keeping
> row 1 active. Then use Format>Conditional Formatting.
>
> In the dialog box that appears, select Formula is and then type in
>
> =not(isblank($a1))
>
> then click on the Format button, select the Patterns tab, and choose the
> color you want to use. OK your way back out to the spreadsheet and try
> entering values in column A
>
>
> "Alex Simpson" wrote:
>
> > Hi,
> >
> > I dont have that great knowledge in Excel but i wanted to try and run a
> > macro in an IF formula but realised that was not possible. I have now got a
> > formula that gives a value in a cell when i want the whole row to turn to
> > red, but im not sure how i can get a macro that will change the whole row to
> > red when that cell shows a value. Can anyone help on this matter?
> >
> > Thanks in advance,
> >
> > Alex

Ok sorry i was being really stupid ignore the last comment, but how do you
make it so that if there is not a value in it, it goes back to the colour it
was before?

Thanks again,

Alex

"Duke Carey" wrote:

> Conditional formatting is your friend
>
> Say you want the entire row to change color (colour) if there is an entry in
> column A. First, select all the rows you want to format this way, keeping
> row 1 active. Then use Format>Conditional Formatting.
>
> In the dialog box that appears, select Formula is and then type in
>
> =not(isblank($a1))
>
> then click on the Format button, select the Patterns tab, and choose the
> color you want to use. OK your way back out to the spreadsheet and try
> entering values in column A
>
>
> "Alex Simpson" wrote:
>
> > Hi,
> >
> > I dont have that great knowledge in Excel but i wanted to try and run a
> > macro in an IF formula but realised that was not possible. I have now got a
> > formula that gives a value in a cell when i want the whole row to turn to
> > red, but im not sure how i can get a macro that will change the whole row to
> > red when that cell shows a value. Can anyone help on this matter?
> >
> > Thanks in advance,
> >
> > Alex

From your question I'm deducing that it isn't really an 'entry' in the
column, right? If you use the conditional formatting option, the pattern
will disappear if you delete the entry in the target cell.

Does your target cell have an =IF() formula whose result returns a blank in
some cases?

"Alex Simpson" wrote:

> Ok sorry i was being really stupid ignore the last comment, but how do you
> make it so that if there is not a value in it, it goes back to the colour it
> was before?
>
> Thanks again,
>
> Alex
>
> "Duke Carey" wrote:
>
> > Conditional formatting is your friend
> >
> > Say you want the entire row to change color (colour) if there is an entry in
> > column A. First, select all the rows you want to format this way, keeping
> > row 1 active. Then use Format>Conditional Formatting.
> >
> > In the dialog box that appears, select Formula is and then type in
> >
> > =not(isblank($a1))
> >
> > then click on the Format button, select the Patterns tab, and choose the
> > color you want to use. OK your way back out to the spreadsheet and try
> > entering values in column A
> >
> >
> > "Alex Simpson" wrote:
> >
> > > Hi,
> > >
> > > I dont have that great knowledge in Excel but i wanted to try and run a
> > > macro in an IF formula but realised that was not possible. I have now got a
> > > formula that gives a value in a cell when i want the whole row to turn to
> > > red, but im not sure how i can get a macro that will change the whole row to
> > > red when that cell shows a value. Can anyone help on this matter?
> > >
> > > Thanks in advance,
> > >
> > > Alex

Yes your right, it is an IF formulae, does that mean it is not possible do
use conditional formatting?

"Duke Carey" wrote:

> From your question I'm deducing that it isn't really an 'entry' in the
> column, right? If you use the conditional formatting option, the pattern
> will disappear if you delete the entry in the target cell.
>
> Does your target cell have an =IF() formula whose result returns a blank in
> some cases?
>
> "Alex Simpson" wrote:
>
> > Ok sorry i was being really stupid ignore the last comment, but how do you
> > make it so that if there is not a value in it, it goes back to the colour it
> > was before?
> >
> > Thanks again,
> >
> > Alex
> >
> > "Duke Carey" wrote:
> >
> > > Conditional formatting is your friend
> > >
> > > Say you want the entire row to change color (colour) if there is an entry in
> > > column A. First, select all the rows you want to format this way, keeping
> > > row 1 active. Then use Format>Conditional Formatting.
> > >
> > > In the dialog box that appears, select Formula is and then type in
> > >
> > > =not(isblank($a1))
> > >
> > > then click on the Format button, select the Patterns tab, and choose the
> > > color you want to use. OK your way back out to the spreadsheet and try
> > > entering values in column A
> > >
> > >
> > > "Alex Simpson" wrote:
> > >
> > > > Hi,
> > > >
> > > > I dont have that great knowledge in Excel but i wanted to try and run a
> > > > macro in an IF formula but realised that was not possible. I have now got a
> > > > formula that gives a value in a cell when i want the whole row to turn to
> > > > red, but im not sure how i can get a macro that will change the whole row to
> > > > red when that cell shows a value. Can anyone help on this matter?
> > > >
> > > > Thanks in advance,
> > > >
> > > > Alex

Yes your right, it is an IF formulae, does that mean it is not possible do
use conditional formatting?

"Duke Carey" wrote:

> From your question I'm deducing that it isn't really an 'entry' in the
> column, right? If you use the conditional formatting option, the pattern
> will disappear if you delete the entry in the target cell.
>
> Does your target cell have an =IF() formula whose result returns a blank in
> some cases?
>
> "Alex Simpson" wrote:
>
> > Ok sorry i was being really stupid ignore the last comment, but how do you
> > make it so that if there is not a value in it, it goes back to the colour it
> > was before?
> >
> > Thanks again,
> >
> > Alex
> >
> > "Duke Carey" wrote:
> >
> > > Conditional formatting is your friend
> > >
> > > Say you want the entire row to change color (colour) if there is an entry in
> > > column A. First, select all the rows you want to format this way, keeping
> > > row 1 active. Then use Format>Conditional Formatting.
> > >
> > > In the dialog box that appears, select Formula is and then type in
> > >
> > > =not(isblank($a1))
> > >
> > > then click on the Format button, select the Patterns tab, and choose the
> > > color you want to use. OK your way back out to the spreadsheet and try
> > > entering values in column A
> > >
> > >
> > > "Alex Simpson" wrote:
> > >
> > > > Hi,
> > > >
> > > > I dont have that great knowledge in Excel but i wanted to try and run a
> > > > macro in an IF formula but realised that was not possible. I have now got a
> > > > formula that gives a value in a cell when i want the whole row to turn to
> > > > red, but im not sure how i can get a macro that will change the whole row to
> > > > red when that cell shows a value. Can anyone help on this matter?
> > > >
> > > > Thanks in advance,
> > > >
> > > > Alex

Alex

Select row 1 then CF

Formula is: =$A$1<>""


Gord Dibben MS Excel MVP


On Mon, 7 Aug 2006 08:38:03 -0700, Alex Simpson
<[email protected]> wrote:

>Yes your right, it is an IF formulae, does that mean it is not possible do
>use conditional formatting?
>
>"Duke Carey" wrote:
>
>> From your question I'm deducing that it isn't really an 'entry' in the
>> column, right? If you use the conditional formatting option, the pattern
>> will disappear if you delete the entry in the target cell.
>>
>> Does your target cell have an =IF() formula whose result returns a blank in
>> some cases?
>>
>> "Alex Simpson" wrote:
>>
>> > Ok sorry i was being really stupid ignore the last comment, but how do you
>> > make it so that if there is not a value in it, it goes back to the colour it
>> > was before?
>> >
>> > Thanks again,
>> >
>> > Alex
>> >
>> > "Duke Carey" wrote:
>> >
>> > > Conditional formatting is your friend
>> > >
>> > > Say you want the entire row to change color (colour) if there is an entry in
>> > > column A. First, select all the rows you want to format this way, keeping
>> > > row 1 active. Then use Format>Conditional Formatting.
>> > >
>> > > In the dialog box that appears, select Formula is and then type in
>> > >
>> > > =not(isblank($a1))
>> > >
>> > > then click on the Format button, select the Patterns tab, and choose the
>> > > color you want to use. OK your way back out to the spreadsheet and try
>> > > entering values in column A
>> > >
>> > >
>> > > "Alex Simpson" wrote:
>> > >
>> > > > Hi,
>> > > >
>> > > > I dont have that great knowledge in Excel but i wanted to try and run a
>> > > > macro in an IF formula but realised that was not possible. I have now got a
>> > > > formula that gives a value in a cell when i want the whole row to turn to
>> > > > red, but im not sure how i can get a macro that will change the whole row to
>> > > > red when that cell shows a value. Can anyone help on this matter?
>> > > >
>> > > > Thanks in advance,
>> > > >
>> > > > Alex

Thank you both very much, you have been a great help!

"Gord Dibben" wrote:

> Alex
>
> Select row 1 then CF
>
> Formula is: =$A$1<>""
>
>
> Gord Dibben MS Excel MVP
>
>
> On Mon, 7 Aug 2006 08:38:03 -0700, Alex Simpson
> <[email protected]> wrote:
>
> >Yes your right, it is an IF formulae, does that mean it is not possible do
> >use conditional formatting?
> >
> >"Duke Carey" wrote:
> >
> >> From your question I'm deducing that it isn't really an 'entry' in the
> >> column, right? If you use the conditional formatting option, the pattern
> >> will disappear if you delete the entry in the target cell.
> >>
> >> Does your target cell have an =IF() formula whose result returns a blank in
> >> some cases?
> >>
> >> "Alex Simpson" wrote:
> >>
> >> > Ok sorry i was being really stupid ignore the last comment, but how do you
> >> > make it so that if there is not a value in it, it goes back to the colour it
> >> > was before?
> >> >
> >> > Thanks again,
> >> >
> >> > Alex
> >> >
> >> > "Duke Carey" wrote:
> >> >
> >> > > Conditional formatting is your friend
> >> > >
> >> > > Say you want the entire row to change color (colour) if there is an entry in
> >> > > column A. First, select all the rows you want to format this way, keeping
> >> > > row 1 active. Then use Format>Conditional Formatting.
> >> > >
> >> > > In the dialog box that appears, select Formula is and then type in
> >> > >
> >> > > =not(isblank($a1))
> >> > >
> >> > > then click on the Format button, select the Patterns tab, and choose the
> >> > > color you want to use. OK your way back out to the spreadsheet and try
> >> > > entering values in column A
> >> > >
> >> > >
> >> > > "Alex Simpson" wrote:
> >> > >
> >> > > > Hi,
> >> > > >
> >> > > > I dont have that great knowledge in Excel but i wanted to try and run a
> >> > > > macro in an IF formula but realised that was not possible. I have now got a
> >> > > > formula that gives a value in a cell when i want the whole row to turn to
> >> > > > red, but im not sure how i can get a macro that will change the whole row to
> >> > > > red when that cell shows a value. Can anyone help on this matter?
> >> > > >
> >> > > > Thanks in advance,
> >> > > >
> >> > > > Alex
>
>