Previous Topic Next Topic

Macro: Setting a cell value from another cell

Closed
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 -
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
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
 803
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
Ask a question