Previous Topic Next Topic

Macro: Setting a cell value from another cell

Closed
Cashel - Sep 19, 2009 at 08:36 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 19, 2009 at 08:46 PM
Hello,
I am creating a spreadsheet for recording annual leave for a department.
I have set up a macro so that when a button is clicked, all the currently selected cells will change colour to denote annual leave.
However, I would like to set the value of each of the selected cells with the value in the cell at the start of the row (and index cell, storing a code)

The macro I have written used is:

Selection.Font.ColorIndex = 41
With Selection.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Selection.FormulaR1C1 = "=RC2"

which works well enough, and sets all the selected cells background and foreground colour to blue and the value in the cell to (for example) =$B7 (if the 7th row was selected.)

However, I would like it to set the actual value in B7 rather than just reference the cell.

Any suggestions?

1 reply

Answer 1 / 1
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Sep 19, 2009 at 08:46 PM
you want to color if any cell value is 87

use find function either as a formulla in speradsheet or in macro

example code statements 

dim cfind as range
set cfind=activesheet.usedrange.cells.find(what=87,lookat:=xlwhole)
cfind.interior.colorindex =41
0