0

enter image description here

Hi

I want to loop through a source range and pickup the actual cell position from the source array, and apply conditional formating on a destination range, with each cell referencing the unique cell in source range. Plase see the picture for description.

So instead of getting the value in "A2" (for instance "1") from range "A2:C3". Id' like to get the positional excel value of "Sheet1!A2" ..B2 etc.

' High level approach

Sub setcondformat()

Dim sourcearr ??, Dim destarr ??, Dim Strpos as String

Dim Strvalue as String ' not necessary, but interesting for educational purposes

For each cell in Source Array do

 select next cell in destination Array    ' eg Sheet2!"J2", "K2" etc ..

  Set conditional format value = Strpos     ' in cond format dialog eg "A2", "B2" etc..

   Next cell sourcearr

end loop


End sub
Improve this question

1 Answer 1

Reset to default
0

So if you all you are really trying to do is return the address of the cell you are currently on in your For Loop, you can just write something like:

For Each cell In SourceArray
    MsgBox cell.Address
Next

Obviously you would want to store the address as a variable, or into an array to use the address later. This will return $A$1 if the cell is currently looping through cell A1. If you were calling the cells value, as mentioned in your questions, you would use cell.value to return the actual visible value in the cell. If this is not what you are after, please let me know and I will attempt to adjust my answer.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

That worked for part of the problem. I tested and yes I can get the cell.address, which is part of what I was looking for. So the flow would be 0 (outside loop- Select 1 destination cell in destination array.) 1 get cell.address from sourcearray as you suggested. 2 add it to conditional format in the destination cell that is currently selected 3 select next destination cell So I need to do a nested loop or similar to move to next destination cell to apply the conditional format in cell 2 in the destination array with the reference to cell two in source array.
Good, that's half the battle then. Can you try to explain a little more what you want done for your conditional formatting? I'm having difficulty understanding exactly what it is you are trying to do, and which range you are trying to apply conditional formatting to. How I am interpreting it is, you want to take the range (A1:C3) take those values and place them on a different sheet, and apply a different type of conditional formatting on them? How are you determining the conditional format, and how do you know what is going where?
Yes, the "problem" is that I have two large range's. One sheet called sheet1(user variables) which has a range of 20*7 cells of values that is going to alter the conditional formats number "rules" in sheet2 (20*7 cells). Selection.FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueFormula Selection.FormatConditions(1).ColorScaleCriteria(3).Value = "=Sheet2!$B$" & i + 3 With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor .Color = 192 .TintAndShade = 0 End With i = i + 1 Loop Continues..
..continues So =Sheet2!$B$" & i + 3 needs to be changed to say = Strpos + 3, and with your suggestion that might work. But I also need to do this on a cell by cell basis. Therefor I need to move to next cell, apply the cell.address in conditional format, move to next cell, apply the cell.address..
Are these ranges both the same addresses, just a different sheet name? I.e., Sheet1!A1:A30 and Sheet2!A1:A30?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.