Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to make a border line on a row based on a value

0

Hi!

I want to make a border line on a row start from column A - F based on the value in cell A1,

let's say if the value in A1 is 2 make the border line by the second row, if A1 is 3 make the border line by the 3rd row, 

so what I try to do I put a OFFSET function in the Conditional Formatting like this

=OFFSET(A1,A1,0,1,6) but it doesn't work, is there any way to make it?

excel
MS Office Version: 2013
posted by: JAExcel (rep: 10) on Thu Aug 17, 2017 at 10:27 am
Answer
Discuss

Answers

0
Selected Answer

You have to format the cell where you wish to have the border, not A1. This is the formula you should apply.

=$A$1=ROW()

If you wish to quote the row number of a given range modify the above to  reflect the row number of the range. For example,

=$A$1 = Row() - 4

If you apply this formula to cell D5, for example, it would respond to the number 1 in A1 becasue ROW(D5) - 4 = (5 - 4) = 1

posted by: Variatus (rep: 4889) on Thu Aug 17, 2017 at 9:45 pm
Discuss

Discussion

Thanks, is works very good, but I need the row number from my range, not from the sheet, lats say I have a range D5:I19, and I want when the user enter the number 3 in cell I21 should the border line be on the 3rd row from this range, and the same think for a lot of ranges
JAExcel (rep: 10) Aug 18, '17 at 11:32 am
Haha. If it works the question was answered. If you have another question accept the answer and ask away.
Variatus (rep: 4889) Aug 18, '17 at 11:44 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login