Using Case and referencing a cell instead of a value

S

S Himmelrich

I'm trying to change the background color of a cell based on a value,
however instead of using the syntax below can you refer to a cell with
a value instead of hardcoding the value in the macro? If you can,
then what is the syntax? thank you in advance

Select Case Range("A1").Value
Case 100
Range("B1").Value = 50
Case 150
Range("B1").Value = 40
Case 200
Range("B1").Value = 30
Case 350
Range("B1").Value = 20
Case 400
Range("B1").Value = 10
Case Else
Range("B1").Value = 0
 
C

carlo

I am not quite sure if that is what you mean:

dim cellrow as long
dim cellcolumn as integer

cellrow = 2
cellcolumn = 2

Select Case Range("A1").Value
Case 100
Cells(cellrow, cellcolumn).Value = 50
Case 150
Cells(cellrow, cellcolumn).Value = 40
Case 200
Cells(cellrow, cellcolumn).Value = 30
Case 350
Cells(cellrow, cellcolumn).Value = 20
Case 400
Cells(cellrow, cellcolumn).Value = 10
Case Else
Cells(cellrow, cellcolumn).Value = 0
end select

you can input data to cellrow and cellcolumn however you want.
if you want to work with letters in cellcolumn do that:

dim cellrow as long
dim cellcolumn as string

cellcolumn = "B"
cellrow = 2

range(cellcolumn & cellrow).value = "bla"


hth

Carlo
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top