Turn a cell to red

[vba317]

I am trying to write a macro that if I click on a cell than run the macro that the cell will turn red or green. I think that I will have to run two macro's named turnred and one turngreen. I tried to record the actions to do this but I did not get the results that I sought. Is it possible to do this?

Sub turnRed()

    Range("D63").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D77").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

 

[SkipVought]

Hi,

sub turnRED()
   activecell.interior.color = vbred
end sub
sub turnGREEN(rng as range)
   activecell.interior.color = vbgreen
end sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[electricpete]

The problem with original macro recording is you tried to paste a format. If you do the action without using paste format you would get closer.

I got this:
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

I'm not sure what the difference is between Selection.Interior.Color and Selection.Interior.Color.Index (can anyone enlighten me).

I tried Skip's and it works fine. Except sub sub turnGREEN takes a range argument which is not used (it works on the activecell).

 

[SkipVought]

OOPS. The GREEN had a superfluous argument. Should look JUST LIKE the RED, except for the color constant.

The difference? Check VBA Help on Color & ColorIndex.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

Thank's I tried Skip's first and it worked. I found the color index property and red does equal 3. But using vbRed is alot easier since I won't have to look up the color index chart. I used activecell.interior.color = 3 and the color was black. When I used ActiveCell.Interior.ColorIndex = 3 I got red. Again thanks for all your help guys. I couldn't do this without your help!


Tom

 

[Gavona]

I'm not sure what the difference is between Selection.Interior.Color and Selection.Interior.Color.Index (can anyone enlighten me).

I used activecell.interior.color = 3 and the color was black. When I used ActiveCell.Interior.ColorIndex = 3 I got red

Excel has a palette of 56 colours 16 of which are for charts. You can see these when you use 'normal' excel functionality. Excel can only use colors from the palette.
Change the palette using Tools,Options,Color.

ColorIndex gives a colour from the current pallet and will change if you change the pallette.
Color.rgb(....) or color.vbRed explicitly sets the color - to the closest match within the current palette.



Gavin