Macro: Setting a cell value from another cell Closed

Question

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?

venkat1926 · Answer

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

Macro: Setting a cell value from another cell

1 response

Similar discussions

Subscribe To Our Newsletter!